SQL Server gets slower until I restart

Question: My SQL Server Instance gets slower over a few days. When I restart the SQL Server Instance – the response times improve. The cycle repeats itself ad infinitum. How can I troubleshoot ? Answer: This is a very common situation. Based on the same workload a SQL Server slowly starts grinding . It’s not a satisfactory situation for anyone. Firstly , the DBA needs to negotiate an agreed time for restart – disrupting server access. Secondly, a regular SQL Server restart suggests there are some underlying issues. Identifying the issues starts with asking the right question. What is happening... Read more →

TempDB - setting the initial size

Question: what should be the initial size of TempB? Answer: The short answer : “depends on the usage” patterns. In general a low usage or workload Tempdb will require a smaller TempDB . A high usage or workload TempDB will require a larger TempDB There is all types of activity occurring in TempDB. Read more on the types of SQL Server Objects using TempDB SQL Server - TempDB performance and strategy checklist The interesting thing about the list of SQL Server Objects is the range of objects. Everything from database mail , UDF and Version Store. The usage of TempDB... Read more →

Monitor SQL Server TempDB usage

Large queries spilling into tempdb such as SQL Server – SORT IN TEMPDB and Sort Warnings and large temporary tables can use Tempdb space very quickly and lead to outages. Use the DMVs : sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, sys.dm_db_task_space_usage How to Troubleshoot TempDB First Step - Comprehensive Tempdb Sizing exercise. There are many different methods , such as : a) create a workload b) running large maintenance jobs e.g index rebuild Second Step - monitoring Tempdb usage. As well as a SQL Server monitoring alerts solution based on events , such as disk full, there are some useful DMVs to drill into... Read more →

3 solutions to SORT_IN_TEMPDB and Sort Warnings that are proven to work

Hours of fun with SORT_IN_TEMPDB and Sort warnings Read more →

SQL Server - BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds

Sometimes you may see this message in the SQL Server Error Log files BobMgrGetBuf Sort Big Output Buffer write not complete after 60 seconds It’s associated with IO subsystem issues , other common errors appearing and related are: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file Read more on IO Requests taking longer than 15 seconds to - SQL Server DBA The message relates to large SORTS , probably the TempDB. Bob is the Big Output Buffer. Writres are heavy sequential and occur on transit tables .During the heavy sequential... Read more →