DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to List CPU Usage Per Database in SQL Server

Posted on May 14, 2016 Written by Andy Hayes 2 Comments

As a DBA there may be a time when you want to try and ascertain how much CPU time each of your databases is consuming. This is useful to complement high CPU investigations or to just try and understand which of your databases overall is the highest CPU consumer over time.

The out of the box reporting that Management Studio provides is sadly missing this report so we have to run some T-SQL to extract it.

As with other DMV’s, the statistics produced are only available from the time when the SQL Server was started and can change over the course of the day. It’s important to remember that these are cumulative figures and may not correlate to any current spike in CPU performance caused by some heavy query.

The DMV can only report on the data that is available in the plan cache. If the plan cache is experiencing lots of churn, the report becomes less accurate.

If you want to track this data over time, you will need to capture data at regular intervals and persist it to some kind of logging table or file.

So here is some code to do it:

WITH CPU_Per_Db
AS
(SELECT 
 dmpa.DatabaseID
 , DB_Name(dmpa.DatabaseID) AS [Database]
 , SUM(dmqs.total_worker_time) AS CPUTimeAsMS
 FROM sys.dm_exec_query_stats dmqs 
 CROSS APPLY 
 (SELECT 
 CONVERT(INT, value) AS [DatabaseID] 
 FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
 WHERE attribute = N'dbid') dmpa
 GROUP BY dmpa.DatabaseID)
 
 SELECT 
 [Database] 
 ,[CPUTimeAsMS] 
 ,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUTimeAs{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}]
 FROM CPU_Per_Db
 ORDER BY [CPUTimeAsMS] DESC;

This is what the results looks like on my test instance.

find cpu usage per database in sql server

I hope you found this useful 🙂

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server

How to Find Buffer Pool Usage Per Database in SQL Server

Posted on May 10, 2016 Written by Andy Hayes 1 Comment

As a DBA it’s important to understand what the buffer pool is doing and which databases are using it the most.

Data in SQL Server is stored on disk in 8k pages. The buffer pool (Aka “buffer cache”) is a chunk of memory allocated to SQL Server. It’s size is determined by the minimum and maximum memory settings in SQL Server memory options:

sp_configure 'min server memory (MB)'
go
sp_configure 'max server memory (MB)';
name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
min server memory (MB)              0           2147483647  0            16

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
max server memory (MB)              128         2147483647  2147483647   2147483647

Data is processed via the buffer pool. When an application requests some data for reading and those pages are not in the buffer pool, the server has to read them from disk. These are known as physical reads. If those pages have been read already into the buffer pool, these are known as logical reads and will typically return to the application more quickly than the physical reads – memory is faster than disk.

The buffer pool is also where modifications are made to pages (dirty pages) before those changes are persisted to storage.

If sufficient memory is not available, pages are aged out of the buffer and space is made available for the pages being read from disk when they are requested. Excessive “churn” of pages in the buffer pool could indicate that it is sized too small. The Page Life Expectancy counter can be used to help diagnose if this is a problem on your server.

The buffer pool is a critical area of the system that must be adequately sized for optimal performance.

SQL Server can tell you how many of those pages reside in the buffer pool. It can also tell you which databases those pages belong to. We can use sys.dm_os_buffer_descriptors to provide this information as it returns a row for each page found in the buffer pool at a database level.

SELECT 
  CASE WHEN database_id = 32767 THEN 'ResourceDB' ELSE DB_NAME(database_id) END AS DatabaseName,
  COUNT(*) AS cached_pages,
  (COUNT(*) * 8.0) / 1024 AS MBsInBufferPool
FROM
  sys.dm_os_buffer_descriptors
GROUP BY
  database_id
ORDER BY
  MBsInBufferPool DESC
GO
DatabaseName                   cached_pages MBsInBufferPool
------------------------------ ------------ ---------------------------------------
ResourceDB                     2968         23.187500
AdventureWorks2012             2614         20.421875
msdb                           438          3.421875
master                         390          3.046875
ReportServer                   332          2.593750
AdventureWorksDW2012           245          1.914062
tempdb                         225          1.757812
PerformanceDW                  205          1.601562
ReportServerTempDB             172          1.343750
Test                           155          1.210937
model                          66           0.515625

(17 row(s) affected)

The data tells us what is in the buffer pool at that moment in time. These results can change quickly depending on the activity of the server. Tracking this data over the course of a day for example would require some kind of job to periodically run this query and capture the output to file or logging table.

The data can be reset if the buffer pool is emptied either upon a SQL Server restart or by executing DBCC DROPCLEANBUFFERS which will clear out the unmodified (clean) pages from the buffer pool.

Emptying the buffer pool of clean pages by running DBCC DROPCLEANBUFFERS will increase load on the disks and decrease application performance until the cache fills again.

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server

Revoking Access for All Users of a Database in SQL Server

Posted on May 9, 2016 Written by Andy Hayes Leave a Comment

I love Management Studio for SQL Server but sometimes it just doesn’t cut the mustard. I had a scenario recently where I needed to revoke access for all users from a database. I could delete them all but that would be more effort than necessary. I just wanted to deny access to a specific database.

To my knowledge, there is not a quick way to do this and so I had to write some T-SQL to do it.

You can revoke a database user using syntax REVOKE CONNECT, for example

REVOKE CONNECT FROM my_user;

What if you have many SQL Server users and you want to stop all of them accessing the database?

Identify them using this query from sysusers. In my case I am using an additional filter to pull out users with a specific prefix.

SELECT name FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

In my case, this returns:

name
-----------
user_1
user_2
user_3

(3 row(s) affected)

To do this, I made a small modification to this query using some concatenation and square brackets in case of weird user names which contain hyphens:

SELECT 'REVOKE CONNECT FROM [' + name + '];' FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

This returned and I know have a series of statements that I can run as one batch:

-------------------------------
REVOKE CONNECT FROM [user_1];
REVOKE CONNECT FROM [user_2];
REVOKE CONNECT FROM [user_3];

(3 row(s) affected)

Copy and paste these into a new query and execute them. Test that access has been revoked either by looking in Management Studio or querying sysusers for hasdbaccess = 1 again.

SELECT COUNT(*) FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

If you’re looking in Management Studio at the users, then a red arrow indicates that the user has been revoked.

evoke access for all users in a sql server database

Filed Under: All Articles, SQL Server Administration Tagged With: sql server

T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()

Posted on July 11, 2015 Written by Andy Hayes 19 Comments

I’ve recently been working on a data migration project and have found myself utilizing the ROW_NUMBER() function in SQL Server a lot. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query.

One of the requirements for an export process was to return the most recent two customer orders for each customer account and outputting this to CSV file.

As well as sequential numbering, ROW_NUMBER() provides a way to partition the results returned so this suited me just fine 🙂

Let’s take a look at the problem in more detail and I’m going to use our good friend the AdventureWorks database to help demonstrate.

The first query returns the data by customer and with the most recent orders for each customer at the top.

SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
  FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] 
ORDER BY CustomerID, OrderDate DESC

Here are the results of this query sampled.

t-sql return top n rows per group sql server

How to return the top two rows for each group in the result set?

We will use ROW_NUMBER() to place a sequential id on the results but also partition the results so that each instance of the customerID value has its sequential id reset.

SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
    ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC) AS RowNum
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

This returns a new column called RowNum which is providing the sequential numbering for each instance of CustomerID based on the PARTITION BY and ORDER BY of the RowNum column.

t-sql return top n rows per group sql server with row_number

All that remains is to now select the first two rows for each CustomerID. So I am wrapping it up in a CTE and returning the first two rows using the WHERE clause.

WITH MyRowSet
AS
(
SELECT SalesOrderID
      ,OrderDate
      ,SalesOrderNumber
      ,AccountNumber
      ,CustomerID
      ,SubTotal
      ,TaxAmt
      ,TotalDue
    ,ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustomerID, OrderDate DESC) AS RowNum
FROM [AdventureWorks2012].[Sales].[SalesOrderHeader] 
)

SELECT * FROM MyRowSet WHERE RowNum <= 2

Which returns our desired result…

t-sql return top n rows per group sql server with row_number and cte

I hope you found this post useful. 🙂

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • …
  • 13
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close