Today I was working with SQL Server 2005 SSAS (SQL Server Analysis Services) and I needed to look into the server to see if there were any issues. My users were telling me that the pivot tables in their Excel spreadsheets were taking longer than usual. Microsoft does not allow you to run like an sp_who in Analysis Services like in the database engine. So Microsoft has come out with a standalone application called Activity Viewer. This is a GUI application that displays users, connections and processes in an instance of Analysis Services for SQL Server 2005. Within the Activity Viewer application it allows you to kill a process just like in the database engine.

The application is written in .NET and you will need to make minor modifications to the application to get it work for your specific environment. Here is the link to download the Activity Viewer application and to get more information:

http://technet.microsoft.com/en-us/library/ms160921.aspx

If you have any problems please post here and I will be happy to assist!

In SQL Server 2005 there several ways to detect a CPU bottlneck.  The first way to detect a bottleneck is by looking at the System Monitor counter Processor: % Processor Time.  If the value of this counter  is greater than 80% for  15 to 20 minutes in duration, then it is a good indication that you have a bottleneck.

You can also monitor System: Processor Queue Length.  A value of 2 or higher for a length of time also indicates a CPU bottleneck.  Another way to detect a CPU bottleneck is to utilize the new DMVs (Dynamic Management Views) in SQL Server 2005.  Execute the  following query in the master database and the query will return the count of number of worker threads in the runnable state:

Select Count(*) AS workers_waiting_for_cpu, s.scheduler_id
From sys.dm_os_workers as o
Inner Join sys.dm_os_schedulers as s
On o.scheduler_address = s.scheduler_address
And s.scheduler_id < 255
Where o.state = ‘Runnable’
Group By s.scheduler_id

I hope this helps… if you have another way to detect a CPU bottleneck post here and let us know!

Today a junior DBA asked me about the files with the .STB extension were for. Then I realized that other DBAs out there may not know what the .STB files are for and what creates them. The .STB files stand for STand By (STB). These files are created when a database is restored with the RESTORE DATABASE command with the clause WITH STAND BY. This statement puts the database in “Stand By Mode” and allows for further backups to be restored.

The caveat here is… when your bring a database online from standby mode the .STB file is sometimes deleted… sometimes it is not. So after you execute the following statement:

RESTORE DATABASE <database name>
WITH RECOVERY

You might still see the file <database name>.stb after the database is brought online. If you still see the file… then you can go ahead and delete the file after the database has been brought online.

When your application experiences a performance problem chances are some aspect of your application has or is hitting a bottleneck. What exactly is a bottleneck? Per Wikipedia a bottleneck is a phenomenon where the performance or capacity of an entire system is severely limited by a single component. A common single component in a SQL Server system could be any of the following:

• CPU
• Memory
• I/O
• tempdb
• Blocking

There are several tools within SQL Server that you can use to monitor and identify which of these single components are the bottleneck. You can use System Montior, SQL Profiler, sp_who or sp_who2. You can now access the sys.dm (Dynamic Management) procedures and write SQL queries to obtain system information in SQL Server 2005. In my upcoming blog posts I will cover these in more detail so please check back often!

Today I was going through one of my production SQL Servers and looking at the connections\spids it dawned on me that some people that are new to SQL Server might not know the different “states” of a worker thread. Here are 3 states that you will come across when executing sp_who or sp_who2:

Running - The worker is currently executing on the CPU

Runnable - The worker is currently waiting for its turn on the CPU

Suspended - The worker is waiting on a resource, for example, a lock or an I/O

These states are good to know in case you need to troubleshoot your system for bottlenecks.

Welcome back and happy new year! Today I wanted to share with you my experience with SQL Server 2005 Reporting Services on a 4 node cluster.

The other day we had failed over one of the nodes to test functionality to see if the Reporting Services databases failed over as well. After the fail-over the Reporting Services databases did fail-over however, Reporting Services could not be found. After researching, we discovered that the databases for Reporting Services for SQL Server 2005 are cluster aware just not the service itself. In order for Reporting Services to work properly in a clustered environment… the Reporting Services needs to be installed on every node in the cluster. So when a fail-over occurs the Reporting Services can be found on the new node and connect to the databases. So I like to call this a “poor mans” cluster aware service.

If you have any experience with this please post and share your experience!

Today I wanted to share with you my “painful” experience with having SQL Server Integration Services (SSIS) installed on a cluster. I just got done installing and configuring a 4 node (3 active 1 passive) cluster. It has Windows 2003 and SQL Server 2005 Enterprise edition installed on it. I got the database engine, reporting services, analysis services and integration services installed and configured properly.

After installing and configuring everything we did a fail-over test. Everything had failed over except for SSIS. Come to find out that SSIS IS NOT cluster aware. You can view the article here that I came across on Microsoft’s website. There are work arounds or “hacks” as I like to call them. Because I am supporting a large production environment and need to minimize downtime as much as possible, we created a separate stand-alone SSIS server. From the SQL Server agent jobs on the production SQL Servers we do RPCs to the SSIS server that kicks off the packages. This has worked great! If anyone has succeeded in the “work arounds” or the “hacks” let us know!

Watch the latest videos on YouTube.com

Today I wanted to share the different ways of maintaining a Disaster Recovery (DR) system. One of the most important functions that a Database Administrator has… is to design and implement a DR plan for a production environment. Planning for a disaster has the following aspects:
1. Creating a backup plan for all databases
2. Setting up a “warm” backup server with the databases that are critical
3. Testing the backups at regular intervals
4. Circulating backups for off-site storage
5. Documenting the backup recovery system and to train co-workers in DR
procedures

Here are the different methods for maintaining a DR system:
1. SQL Server Cluster (SQL Server 2000 and 2005)
2. Warm Standby Server (SQL Server 2000 and 2005)
3. This can be maintained by log shipping, RPCs (job based), Transaction
Replication or through DTS or SSIS.

DR enhancements in SQL Server 2005
1. Database Mirroring
2. Peer To Peer Replication

I suggest to read up on each of these methods and decide which DR system fits your back-end infrastructure.

Welcome back to squirrel’s blog on “everything” SQL Server. I will be covering little nuances with Database Mirroring here. Database Mirroring is a very cool feature in SQL Server 2005 when you get it setup and configured properly. I went through the growing pains of installing, configuring and troubleshooting this process.

Before I installed it on my production database servers I used an application called VM Workstation - http://www.vmware.com. With this cool software I was able to create the production SQL Servers (here you will need the Windows and SQL Server ISOs) but in a virtual form. My production database servers have Windows 2003 Server and SQL Server 2005. Once I got 3 (Principal server, Witness server, Mirror server) virtual machines\servers setup I went ahead and installed and configured Database Mirroring. Import thing to note… you will need to install SQL Server 2005 Service Pack 1 or later for database mirroring to work. Database Mirroring is disabled by default.

When setting up the servers to participate in the mirroring process it is import to fully qualify the server name e.g. mycompname.mydomainname in order for Database Mirroring to see the servers. I tried it using the IP Addresses but Database Mirroring did not like that. Also, fortunate for me all of my servers were in the same domain. If anyone has experience going across different domains please share your experience. Last thing to note on Database Mirroring… when using a Witness server if there is a ‘hick up’ in the network the Witness will view that as a failure in the communication between the Principal and Mirror servers and will automatically “fail” it over to the mirror server. For this reason, I have left the Witness server out of the configuration and just have the Principal server and the Mirror server. If there is a production problem then you can fail it over manually.

For more information on Database Mirroring please see BOL.