• Capacity Planning for tempdb
• Optimizing tempdb Performance
• Troubleshooting Insufficient Disk Space in tempdb

Be sure to check them out!

sys.dm_os_wait_stats

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

What caught my eye was the section concerning the number of files for TempDB. Although it isn’t a new concept to me, it is still intersting to get more feedback on this particular issue. Read on at your leisure!

http://msdn.microsoft.com/en-us/library/dd758814.aspx

If you weren’t interested in Windows 2008 before, you should be now. The conclusion also has an interesting section, and I quote: 

“Windows Server 2008 aligns partitions by default. When servers are upgraded to Windows Server 2008, preexisting partitions are not automatically aligned and must be rebuilt for optimal performance. Thus, until existing misaligned partitions created using Windows Server 2003 or Windows 2000 Server are rebuilt properly, disk partition alignment will remain a relevant technology.”

Let me know what you think, looks pretty exciting to me!

http://www.codeplex.com/PAL

This undocumented command in TSQL reads your log file and returns results. If you’ve ever used a log reader such as the one from APEX, or any other log reader for that matter, you will see the resemblence. Here’s the syntax on how to call this particular function:

SELECT *

FROM ::fn_dblog(NULL, NULL)

sp_help fn_dblog

You can find the Microsoft link here:

Here is the document for your reference as well: tshootperfprobs

First, CPU bottlenecks can occur from a variety of sources, including:

• Nonoptimal query plan
• Poor SQL Server configuration
• Insufficient hardware resources
• Nonoptimal schema design

The best approach to determine if your SQL Server is CPU bound (meaning the CPU has reached 100% utilization) is to utilize System Monitor in Windows and review the counter called Processor: % Processor Time. Values greater than 80% indicate your system has a CPU bottleneck. In addition, you can also use sys.dm_os_schedulers to find out how many runnable tasks exist. Values other than zero indicate that tasks are waiting to run, and high values are an indication that the CPU is bottlenecking your performance. This query will identify the number of runnable tasks:

SELECT

    scheduler_id,

    current_tasks_count,

    runnable_tasks_count

FROM

    sys.dm_os_schedulers

WHERE scheduler_id < 255

Additionally, the following query shows which batches or procedures are consuming the most CPU and will also include the actual SQL statement. The query aggregates by a specific plan handle. If the plan handle contains more than one SQL statement you must drill into each statement to determine where the greatest CPU contribution comes from.

SELECT TOP 50

   SUM(qs.total_worker_time) as total_cpu_time,�
   (SELECT [TEXT] FROM sys.dm_exec_sql_text(plan_handle)),
   SUM(qs.execution_count) as total_execution_count,
   COUNT(*) as number_of_statements,�
   qs.plan_handle
FROM sys.dm_exec_query_stats qs
GROUP BY qs.plan_handle
ORDER BY SUM(qs.total_worker_time) DESC

Additional consideration must be given to excessive query recompilation, innefficient query plans, intra-query parralelism and cursor usage when considering CPU bottlenecks. Please refer to Microsoft’s article titled Troubleshooting Performance Problems in SQL Server 2005 for more detailed information.

A few months back our firm decided that performance was enough of a problem for our product that we figured hiring a Microsoft consultant to come out would be a good idea. We learned a lot from the analyst, not so much in terms of things we didn’t already know, but really, useful tools to help make our performance tuning easier. Along with the Performance Dashboard, which I ultimately wrote an entire training manual on, Mr. Microsoft Guy also told us about DMV Stats. If you are already aware of this tool, please stop reading now. However, if you are interested in knowing what it is, check out this link…

DMVStats 1.01

That link takes you to Codeple, where Microsoft has uploaded a series of reports that run within SSRS (SQL Server Reporting Services). First thing first, you need SSRS installed and running! Also, it needs to be patched up to the same level as the rest of your SQL Server 2005 system (assuming you are running SQL 2005). May I suggest not using RTM? I had a nasty time of it at my last job when it came to installing and running SSRS. For the love of all that is good and right in the world, download your service packs and apply them.

Also, before you install SQL DMV Stats you’ll need to set up SSRS. Never done it before? Follow this link to determine how:

How to install and how to configure SQL Server 2005 Reporting Services on a computer that is running Windows Server 2008

Really, it is simple once you have the service packs installed.

Other gotchas include setting up DMV Stats after it is installed. Make sure to look at the zip file after you download DMV Stats. There is a read me file in there that explains a few problems. For example, if you have a non-default server install, which could be as little as using a named instance, then you’ll need to address that within a DOS batch file.

Next, after you get the DMV Stats installed you’ll need to configure it. How do you configure that within SSRS you ask? Simple, the reports that run are interactive. There are a few concepts you should be aware of so read the documentation carefully. There are snapshots, samples and baselines. Baselines are uber importante! Make sure you have them. Also, you need to turn on the sql agent jobs. That means, if you are like me and are searching for every ounce of memroy you can muster up on your PC, then you have SQL Agent turned off…. ha, that day has come and gone.

You’ll need to have SQL Agent turned on in order for DMV Stats to work. However, you can adjust the timing of when the jobs are run, check the documentation. Also, each database on your server has to be turned on for collection. That’s the Configure Baselines report  (check the name on that, I’m not close to my work PC at the moment).

That leads me to another gotcha… don’t turn on too many databases for monitoring, your IO will go crazy! I frequently had system freezes as I run Virtual PC, SQL Server (both in and outside of the virtualized system) and so you can see how much IO there is. The procedures tied to DMV Stats are somewhat IO intensive so I would suggest you keep a handle on exaclty how much of your available disk IO you have.

Lastly, I’ll state my opinion on DMV Stats. I liked it, but let’s be honest, it’s not Quest Software’s Performance Analyzer tool. I like that it is a free tool, but the resources required to run it on a single box were pretty intensive. I saw the ASP.Net worker process tied to the report generation reach over 400 Mb. Furthermore, the IO quickly rose to the top of my demand, leaving me with little resources to perform other activities (this was clearly because I configured too many databases for it to monitor).

I’ll say one thing for certian, the Performance Dashboard is quite competitive, but the DMV Stats leave some room for improvement. I might blog some more on this later when I get some more time, but that’ll have to do for now. Hope this helps.

Cheers,
Bob