This is hardly a new topic and whilst there are a lot of articles out there instructing us on how to stripe tempdb etc I feel there’s a bit of a gap on exactly how to identify a problem with tempdb in the first place and particularly on finding out how much of your overall IO tempdb is responsible for.
I cover that in this post but it is also covered more extensively in my SQL Server and Disk IO series of posts, as the troubleshooting and monitoring principles covered in them are just as valid for tempdb performance monitoring as they are for application databases.
Finally got some breathing space for my first post of the year!
In order to get a major client through Christmas which is traditionally their busiest period of the year (where load normally goes up five-fold), we embarked on a major platform refresh where both the back end SQL Server DBMS was upgraded from SQL 2008R2 to SQL Server 2012 and the hardware was upgraded to 2 x Dell PowerEdge R910s with 4 x 10 (physical) cores and 512GB of RAM in each server.
Carrying on my series of posts on SQL Server and Disk IO it’s time to cover the old stalwart that is perfmon (referred to in Windows as Performance Monitor) which I know anyone who has delved into any Windows performance issue will have some familiarity with, so I’m not going to cover what it is or how to use it.
The principal reason for this post is to add my own experiences of using perfmon to turn it into a sql performance monitor to track down issues and monitor SQL Server performance.
Do you have tempdb on Fusion-io (or equivalent) technology? Is your reindexing job left on default settings (re-orgs at 5-15% and full rebuilds at @ 30%)? If so, then you might be interested in the findings in this post.
The benefits of flash memory as a replacement for spinning drives in order to boost SQL Server performance is well documented. However, I’m not sure if one particular benefit has been fully exploited.
The single biggest clue to the source of a performance problem will be from something referred to as the wait stats.
What are waitstats? In short wait stats are statistics gathered on what resources SQL Server is waiting on internally whilst executing queries.
Why are they useful? They can often provide very quick and objective evidence of a performance bottleneck by showing cumulative wait times that SQL Server spent on getting access to the CPU, memory or disk access as well as numerous other internal resources.
I’m starting off this series of posts with a discussion about partition offsets. Diving off at the deep end a bit perhaps, but if your disk setup is not based on firm foundations, you’re setting yourself up for one of the most common and easily-avoidable performance issues right from the off, and a IO performance hit of 20-30%.
The partition offset issue is relevant to any disk partitions created prior to Windows Server 2008.
Many aspects of a database system’s configuration will affect the performance of queries running on that DBMS. However, there is one single component that has the greatest impact on DBMS performance, and that, of course, is disk (IO) access.
It’s a broad topic and all aspects of it are pretty well covered in various blogs and technical articles, but there’s not much out there that gathers it all together. The intention of this series of posts is to try consolidate some of this information and focus in on some of the functions and procedures that can be used to monitor, troubleshoot and configure SQL Server disk configuration and most, importantly, disk performance.
SQL Server and CPU core usage How many CPU cores can SQL Server support should be a simple question, you might think. However, judging by how often this question is asked on the SQL Server forums its clear that there is a lot of confusion about exactly how many CPUs a particular edition of SQL Server can use.
The problem is due mainly to incorrect assumptions about Microsoft’s licencing policy, combined with the prevalence of multi-core CPUs.
[NOTE: This is an old post that is no longer actively maintained and covers versions of SQL Server that are no longer in support. I’m leaving it up because it’s generated a lot of comments over the years which may contain useful information for those folks having to maintain legacy systems.]
Correctly configuring SQL Server memory settings is critical for server performance, but one of the things that I frequently come across when reviewing SQL Server installations whilst working in CSS for MS, is just how many of them had not been set up with appropriate memory configuration settings, or, as in many cases, not set up in the way the administrators of the system had assumed they were; usually the DBAs thought the system was set up to use all e.