Tempdb is re-created each time the instance of SQL Server is started; it’s not required to physically move the data and log files. The files are created in the new location when the service is restarted. Until the service is restarted, tempdb continues...
Read More
Script to Check TempDB Speed
Below script shows TempDB data files and lists out how fast they’re responding to write (and read) requests: [crayon-5b52ff1b52300992111828/] We’re looking for two things: Are writes being evenly distributed between data files? Are writes finishing in 20ms or less? If the answer is no to...
Read MoreSQL Server buffer pool
SQL Server uses a buffer pool of memory to cache data pages, reducing I/O demand and improving overall performance. SQL Server buffer pool also called as buffer cache is a place in the SQL Server system memory that is used...
Read MoreLog shipping Alerts failing to send emails
Recently I had to configure Database mail in one of the production SQL Server estate to send Log shipping Alerts. Database email has been configured and it was successfully sending test emails through the SQL Server. However when I try...
Read MoreView queries waiting for memory grant
One of the smartest ways to identify memory consumption issues in SQL Server is checking details of the queries waiting for memory grant. Below script will show all the queries that have acquired a memory grant or that still require...
Read MoreSQL Server Patching
It’s important to install SQL Server service packs whenever they are available and keep database servers up-to-date. Service Packs contain important fixes and they are tested rigorously than incremental releases like cumulative updates, so they’re pretty important to install and...
Read MorePoorly performing queries in SQL Server
Today junior Application Developer asked me what are the causes of poor performance queries in SQL Server? This is a very open ended question and there could be a lot if reasons behind the poor performance of a query....
Read MoreSQL Server Memory Dumps
Memory dumps are a copy of what contain in the RAM during SQL or system crash or even a manual dump. Memory dumps are useful when working with any SQL server troubleshooting because it helps to identify exactly what was...
Read MoreTotal Server Memory and Target Server Memory
Total server memory specifies the amount of memory the server has committed using the memory manager. Target Server Memory Indicates the ideal amount of memory the server can consume. Total Server Memory divided by Target Server Memory ratio should be close...
Read More