AUTO_CLOSE and performance issue in a .NET application

One of the interesting scenario I worked recently. .NET application which has multiple tabs showing employee details shows poor performance when run for the first time during anytime of the day but when we connect to SQL Server Management Studio and run any ALTER COMMAND, then switch back to application, performance is as expected. This…

2

Best Performer: Distributed query (Four-part) or OPENQUERY when executing linked server queries in SQL Server

I wanted to share this information to all those who are searching for better performer when it comes to distributed query vs openquery for running linked server queries. Distributed Query : Linked server four part queries are also called distributed queries. Using distributed queries,  you can refer tables on different data sources/servers in a single…

6

T-SQL Query Script to monitor Memory Usage of a SQL Server Instance

If you are using Task Manager to monitor the Memory Usage of SQL Server, please note that Working set shown in Task Manager for SQL Server does not include memory committed by SQL Server using AWE allocations and Large pages. Also “Total Server Memory” Performance Counter is also not the total memory used by SQL…

32

Query to find what modules are loaded inside SQL Server VAS (Virtual Address Space)

Code of sqlservr.exe and any other dll’s it depends on need to be loaded into SQL Server memory space for execution. So if you are having VAS fragmentation, there are multiple causes and one of the cause is numerous dll’s loaded by extended stored procedures. So to find what dll’s are loaded and how much…


Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.   I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also…

1

Convert Page ID offset address to Page Number

Here is a handy reference for converting Page ID offset address to corresponding Page Number in SQL Server 2000: As per http://support.microsoft.com/kb/828339: <offset>: This is the physical byte offset from the start of the file. Dividing this number by 8192 will give you the logical page number that is affected by the error. Let’s try…


Troubleshooting slow running query using Extended Events Wait info event

Extended Events introduced in SQL Server 2008 is a detailed event infrastructure for SQL Server. If your environment is running with more SQL Server’s of version > 2008, it is the right time to learn about Extended events since they help you a lot in troubleshooting SQL Server performance issues. In general there are two things possible in…

1

Script existing server side trace to create a new trace with same definition

I wrote this script today and thought of sharing since it might be useful for you. I wrote it because there is no option to set rollover of a existing server side trace and we have lost the original script which created this server side trace in SQL Server. So idea is to script existing…