I encountered an interesting question over on the MSDN forums concerning a poster that was reporting experiencing an issue whereby the “sa” account kept being locked out. In scenarios such as this the SQL Server Error Log is your friend, as it can be configured to record failed login attempts for an instance. Of course […]
What Are You Waiting For?
I know what I am waiting for. I know exactly what every SQL Server instance in my care is waiting for. Why? Because it’s my job to make SQL Server go faster. Identifying what your SQL Server Instance is waiting on will show you exactly where to look in order to make your server go […]
How much memory is each SQL Server database using?
Whilst perusing the forums over at SQL Server Central today I stumbled across an interesting question regarding how to identify how much memory is being used by SQL Server on a per database level. As you know SQL Server stores database data and index pages in memory in an area know as the Buffer Pool. […]
SQL Snippet: SQL Server Wait Types
Here is a handy little SQL Snippet that will return information about those all important SQL Server Wait Types for your server. It uses the SQL Server Dynamic Management View (DMV) sys.dm_os_wait_stats in order to extrapolate the desired information. A column has also been added to provide details of the percentage of total wait time […]
DMV Query to identify all active SQL Server Sessions
You may already be aware that I am a big advocate of the SQL Server Dynamic Management Views (DMV’s) and the benifits that they bring to the Database Administrator. They certainly came in handy with a requirement I had recently whereby I needed to identify the IP Address of a specific SQL Server Login and […]
How to identify the most costly SQL Server queries using DMV’s
I was investigating a performance issue recently and so thought I would share the T-SQL query that I created with you. There’s nothing new or magic here, the code snippet simply identifies the top 20 most expensive queries (currently cached) based on the cumulative CPU cost. The query returns both the SQL Text from the […]