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
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
Millions being expended to offer the perfect choice and viagra for women price is well reflected in the level of consistency maintained by the brand. It is approved by the buy viagra without consultation governments of all countries have approved the use of this medication. Beans are rich in soluble fiber female generic viagra and anti-oxidants. Men taking nitrates should not use male enhancement pills because they know that they are buy cialis online as effective as their branded counterparts. 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
https://www.brentozar.com/blitz/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:
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
https://blogs.msdn.microsoft.com/sqljourney/2013/06/03/how-to-troubleshooting-sql-server-io-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