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!

Well I have decided like many DBAs to get my MCDBA 2000. I have started to study for the 70-228 exam and I have come across study guides with some sample questions. I thought I would share some of the questions with you to see if you can answer them as well. In my next blog post I will reveal the correct answers. Here are 2 questions:

Question 1:

You are the administrator of a SQL Server 2000 computer. You have two new hard disks on which you will create a database named Inventory. You want to insert, update, and delete data as quickly as possible. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

a. Configure the hard disks as two mirrored NTFS volumes.
b. Configure the hard disks as one mirrored NTFS volume.
c. Configure the hard disks as two independent NTFS volumes.
d. Configure the hard disks as one extended NTFS volume.
e. Place Inventory_data.mdf on the first volume and Inventory_log.ldf on
the second volume.
f. Place Inventory_data.mdf and Inventory_log.ldf on the first volume and
Inventory_data2.ndf and Inventory_log2.ldf on the second volume
g. Place Inventory_data.mdf and Inventory_log.ldf on the same volume.

Question 2:

You are the administrator of a SQL Server 2000 computer. The server contains seven databases that provide data to several company client/server applications. Each database is maintained by a different user. You need to configure server and database permissions so that each user who maintains a database has full permissions on that database. What should you do?

a. Create a domain user group for all users.
Add a login for the domain user group.
Add the login to the sysadmin server role.
b. Create a domain user group for all users.
Add a login for the domain user group.
Map the login to a database user in each database.
Add all database users to the db_owner database role in each database.
c. Create a login for each user’s domain user account.
Map each login to a database user in the appropriate databases.
Add the database users to the db_owner database role in each database.
d. Create a login for each user’s domain user account.
Add the logins to the dbcreator server role.

Good luck on answering these questions and I hope you get them right. I will update you on my progress as I finish each test and I will give my feedback on the exams here. So keep checking back!

Hello again… I hope you had a good holiday weekend for those of you who had the holiday off. Last night I came across a stored procedure that used SQL Server reserved words in the SELECT and INSERT statements. Since I am using SQL Server 2005, the words that came up as “reserved” were:
status
views
subject
user_id

If you must use SQL Server reserved words put brackets [] around them. Putting reserved words in brackets [] is a “tuning” technique that DBAs use to “optimize” T-SQL code. If you do NOT put brackets [] around the reserved words then SQL Server will need to take the time to “resolve” the word and decide if it is being used as a reserved word or if it corresponds to a column in a user table or a literal in a result set. This will take SQL Server longer to execute the T-SQL script. If the reserved words do in fact have brackets [] then SQL Server knows that it corresponds to a column name or as a literal if you use it as a column heading in a result set and will not have to do a compare.

I hope this helps you out… if you have any other way to tune or optimize please comment!

Hello and welcome back to my blog… today I was needing to change the port number on one of my production SQL Server 2005 boxes. Wouldn’t you know it… I had to look for it because it has been awhile since I have had to change the port number on the server. So I thought today I would share where you will find the server port number in SQL Server 2005.

From the Start button click on All Programs, then click on Microsoft SQL Server 2005. From there navigate to Configuration Tools and click on SQL Server Configuration Manager. Once the Configuration Manager opens up, expand the SQL Server 2005 Network Configuration. Underneath this you will see Protocols for <your server name> … click on that. This will display the information for this server on the right hand side of the Configuration Manager. On the right side, double-click on TCP/IP. After double-clicking this the TCP/IP Properties dialog box opens up. Click on the tab that reads “IP Addresses” and this will display all of the IP addresses that are assigned to this server. If you have multiple IP addresses assigned (depends upon how many nic cards you have in the server) to this server then scroll to the bottom to where it reads “IPAll“. Underneath this you will see where it reads “TCP Dynamic Ports“… this is where the Server Port number is located and this where you make the change.

I hope this helps and you can use this a reference each time you need to find the server port.

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!

Hello and welcome back again…

After a little break I thought of what else people would be interested in and I thought about education. In my years of experience I have not seen an employer “want” or more to the point “request” a person that has an MCDBA. Employers seem to “want” people with “real world” experience as opposed to a “paper” DBA. Now the credential is a great idea by Microsoft and I am sure the certification gives employers the “warm fuzzies”. I can see the MCDBA helping a beginner obtaining a junior level DBA position or a seasoned DBA to getting a promotion.

I am all for education and getting as much as you possibly can. Not only does it benefit the employer indirectly but benefits YOU! I definitely believe the cost of the exams are worth it because it will benefit you in the long run. If you ever go for a job and it is between you and another candidate with the same experience chances are the employer will pick you because of the certification.

Let me know your take or opinion on the MCDBA certification!