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
Posts in the [ MS SQL DBA ] Category
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-5f7600080b05c263839794/] 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 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 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 MoreMemory Grants Pending Counter
Memory Grants Pending Counter indicates the total number of processes waiting for a workspace memory grant. The recommended Memory Grants Pending value is zero, meaning no processes are waiting for the memory. Anything above 0 indicates that there are processes...
Read MorePage Life Expectancy Counter
Checking the Page Life Expectancy (PLE) counter in SQL Server is one of the greatest ways to identify memory consumption issues. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. If...
Read MoreBuffer Cache Hit Ratio
Buffer Cache Hit Ratio is the percentage of SQL server pages requested and retrieved from the buffer cache without reading from disk. Reading data from disk is slower than reading from memory. High Buffer Cache Hit Ratio indicates a large amount...
Read MoreFind SQL Server logins that have blank passwords
Today our data security officer asked me to get list all SQL Server logins that have blank passwords and SQL Server logins that have password exactly same as username. Below script can be used to retrieve those weak SQL Logins.
Blank... Read More
Compressing Backups
SQL backup files can quickly become very large and it might take a lot of space, which can cost quite a lot of money, so SQL Server enables you to compress them. You can set the default backup compression behaviour and...
Read More