The SQL Server error log folder is increasingly used to store other files such as the System Health monitor’s XEL event files as well as the default location for Extended Event traces. I’ve seen some really creative approaches to getting this information over the years, so I thought I’d share my slightly more boring version which works as far back as SQL Server 2008: /* Script to get the error log directory (pre-SQL Server 2012) */ declare @logpath nvarchar(1024) select @logpath = convert(nvarchar(1024),serverproperty('errorlogfilename')) select @logpath = left(@logpath, (len(@logpath) - charindex('\\',reverse(@logpath)))) select @logpath as errorlogfolder There’s not much to it because it’s using the ErrorLogFilename property of the SERVERPROPERTY function to do most of the groundwork.

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.

At a client site recently a SQL Server 2012 (Enterprise, SP1 + CU#9) server with 512GB RAM and 80 cores (40 cores assigned to SQL Server) that had been patched and restarted displayed the following symptoms after an index reorg operation was restarted: CPU saturation, with all cores available to SQL Server (but not all cores on the box) almost totally maxed out and the perfmon data showing an average of 96% CPU usage for those CPUs.

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.

If you want to trace a SQL Server login failure (18456 error) you’re going to have to get to grips with SQL Server Audit from now on. This deprecation of SQL Profiler provided a suitable opportunity to update my original post on isolating login failures in order to show how to trace login failures using SQL Server Audit, but as it would have bloated that post way too much I’ve created it as a separate post.

I’ve finally got around to this, the third part of my SQL Server and Disk IO series of posts: The sys.dm_io_virtual_file_stats DMV and fn_virtualfilestats function are great tools for extracting granular information about SQL Server IO performance, right down to the individual file in each database. It gives details of the number of physical reads/writes, amount of data read/written and how much time was spent reading/writing that data. Traditionally, this has been important because IO access is the slowest part of fulfilling any database queries as RAM and CPU access takes nanoseconds or microseconds, whereas typically access to a spinning disk takes several milliseconds.

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.

For things like automated SQL Server build scripts (and probably many other reasons) finding the root data and log file paths is essential. Prior to SQL Server 2012 there were all sorts of options ranging from e.g. running sp_helpfile against the master database (which is not foolproof) or constructing a string to establish the right registry key to search (which can be a pain for named instance) and then running an undocumented extended stored procedure called xp_instance_regread to get this information.

When I worked in CSS for MS there were a lot of cool internal tools we could use to help us in our day to day work, and this one tool is great for Microsoft error codes. Over the years some of these have been made public – the most famous of which is PSSDIAG on codeplex, which was released with some fanfare (at least within the SQL community). However, there’s one which is (arguably) even more useful as it doesn’t just apply to SQL Server.