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.
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 listeners Verifying and troubleshooting SQL Server listener connectivity via tcp, named pipes or shared memory connections is a key component in a DBA’s armoury but often overlooked until it’s too late and application teams call up complaining their apps can’t connect.
SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes, but sometimes it’s necessary to check connectivity by a specific listener.
With a little help from SQL Server’s tracing tools you can easily get to the bottom of almost all login failures (18456 errors).
If the login failure is against SQL Server 2005 or above and the default trace is running just go to my SQL Server default trace post.
If you’d prefer to use extended events (as SQL Profiler is now deprecated), go to Capturing login failures via SQL Server Audit.