High amounts of memory paging

Question

High amounts of memory paging

jsheldon

SSCertifiable

Points: 5250
More actions
September 16, 2008 at 8:17 am

#212693

We have a SQL server that has a hodgepodge of applications. One of the biggest application is SharePoint and as we continue to use our SharePoint server we have the need to move off the other applications so it will eventually become dedicated.
Looking for a delivered sp or script to help me track down the very high memory paging occuring on the SQL server and the specific databases that are causing the high latency.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

jsheldon (9/16/2008)
We have a SQL server that has a hodgepodge of applications. One of the biggest application is SharePoint and as we continue to use our SharePoint server we have the need to move off the other applications so it will eventually become dedicated.
Looking for a delivered sp or script to help me track down the very high memory paging occuring on the SQL server and the specific databases that are causing the high latency.

Check out these links:

http://msdn.microsoft.com/en-us/magazine/cc135978.aspx

http://www.sswug.org/whitepapers/papers/WP_MakingSenseSS_final_1.pdf

From the 1st link:

Databases with most I/O:

SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;
SELECT TOP 10
[Total Writes] = SUM(total_logical_writes)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Writes] DESC;

Most expensive I/O queries:

SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

jsheldon SSCertifiable Points: 5250 More actions · Answer 2

Thanks...when you showed me this the lightbulb went off in my head and I remember I do have these performance queries I will execute then review

Thanks again

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

I highly recommend the free Performance Dashboard.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

jsheldon SSCertifiable Points: 5250 More actions · Answer 4

I have the Server Dashboard under Reports on manager studio but not the performance I think I have to download this from MS website...am I correct?

do you have the link?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

Yes, you have to download it from Microsoft. I don't have the link - but if you search Microsoft for Performance Dashboard it comes right up.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs