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.
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.
The SQL Server Error log At some point every DBA will have had to look at the current SQL Server error log using their favourite text editor. However, as the default error log file is literally just called errorlog (with no file extension), there’s the awkward process of selecting the application with which to open the file every time you need to view it, with no option to associate errorlog with your favourite text editor thanks to the lack of file extension.
Working out when a database backup or restore operation will take to complete can be a real pain. Fortunately, the sys.dm_exec_requests DMV has some useful columns which can help us predict this. Here’s the query I put together whilst waiting for my backup performance tests to complete:
SELECT r.session_id, CONVERT(NVARCHAR(22),DB_NAME(r.database_id)) AS [database], r.command AS [backup_process], s.last_request_start_time AS [started], DATEADD(mi,r.estimated_completion_time/60000,getdate()) AS [finishing], DATEDIFF(mi, s.last_request_start_time, (dateadd(mi,r.estimated_completion_time/60000,getdate()))) - r.wait_time/60000 AS [mins left], DATEDIFF(mi, s.