SQL Server diagnostics notes

This week’s amusement: performance of a recently-implemented Stored Procedure dropped and dropped to intolerable levels. This system cannot be easily upgraded. Significant fragmentation is involved. Memory pressure is involved. Inserts to table variables in multiple stored procs are involved. This post records some investigative googling.

Adjusting file autogrowth in SQL Server production systems:

https://serverfault.com/questions/31499/can-i-change-a-live-sql-database-from-autogrow-safely

Scope of Temporary Objects:

https://www.google.com/search?q=sql+server+scope+of+temp+tables&oq=sql+server+scope+of+&aqs=chrome.0.0j69i57j0l4.8990j0j4&sourceid=chrome&ie=UTF-8

Scope of table variable and temp table https://social.msdn.microsoft.com/Forums/sqlserver/en-US/02337dd5-5cfd-40d8-b529-12dc557d6a7e/scope-of-table-variable-and-temp-table?forum=sqltools

Temp tables and table variables scope and persistence

https://www.sqlservercentral.com/Forums/Topic1180410-392-1.aspx

http://www.toadworld.com/platforms/sql-server/w/wiki/10282.temporary-objects

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/02/19/check-if-a-temporary-table-exists

IF object_id(‘tempdb..#MyTempTable’) IS NOT NULL

BEGIN

<TSQL Batch>

END

http://www.toadworld.com/search#q=temporary%20objects&group=6

 

Investigating performance of stored procedures:

https://www.google.com/search?q=sql+server+which+stored+procedures+are+running&oq=sql+server+which+stored+procedures+are+running&aqs=chrome..69i57j0l5.7751j0j7&sourceid=chrome&ie=UTF-8

https://www.red-gate.com/simple-talk/sql/performance/which-of-your-stored-procedures-are-using-the-most-resources/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql

https://stackoverflow.com/questions/25846306/check-if-stored-procedure-is-running

After a bit of research on MSDN (https://msdn.microsoft.com/en-us/library/ms189497.aspx) I was able to figure out the following query that will provide the list of running processes:

select
object_name(st.objectid) as ProcName
from
sys.sysprocesses as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where
object_name(st.objectid) is not null

Currently running procedure name: https://www.sqlservercentral.com/Forums/Topic813548-146-1.aspx

Fragmented database files / VLF counts:

https://www.google.com/search?q=reduce+number+of+vlfs&oq=reduce+number+of+vlfs&aqs=chrome..69i57.5927j0j7&sourceid=chrome&ie=UTF-8

High Virtual Log File (VLF) Count

http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/ including :

How Do I Lower a Database’s VLF Count?

Once armed with a list of databases that have high VLF counts, the next step is to shrink the logs to as small as possible then grow them back to the original size, ideally in a single growth. This is best done during off-peak times

Transaction log performance:

https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

https://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/

Average Disk Queue Length:

https://www.google.com/search?q=sql+server+disk+queue+length+high&oq=sql+server+disk+queue+&aqs=chrome.2.0j69i57j0l4.5140j0j7&sourceid=chrome&ie=UTF-8

https://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/

https://www.sqlservercentral.com/Forums/Topic1296901-146-1.aspx

SQL Server disk performance metrics – Part 2 – other important disk performance measures

How To: Troubleshooting SQL Server I/O bottlenecks

https://dba.stackexchange.com/questions/17629/cause-of-high-disk-queue-length-in-sql-server

Increase speed of inserts:

https://www.google.com/search?q=sql+server+do+indexes+speed+up+inserts&oq=sql+server+index+spedd+up+&aqs=chrome.2.69i57j0l5.9895j0j7&sourceid=chrome&ie=UTF-8

https://stackoverflow.com/questions/7090243/sql-insert-speed-up

Indexes on Table Variables: https://stackoverflow.com/questions/886050/creating-an-index-on-a-table-variable

Indexes on Temporary Tables: http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx

 

This entry was posted in Linkdump, Programming, SQL Server, System/DB Administration. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *