SELECT*

The below link is explaining about the "Wait Stats" of SQL Server which was described by Paul Randal. Analyzing sys.dm_os_wait_stats

Hi In this post i am analyzing the " sys.dm_db_index_physical_stats " function results by with some production data. By seeing this we can come to below conclusions. First condition is 1) Focus on those rows which has page_count >1000 on those rows you can come to below conclusions. If fragmentation(avg_fragmentation_in_percent) is less than 5 % (avg_fragmentation_in_percent)--> Leave as it is If fragmentation(avg_fragmentation_in_percent) is more than 5 % and less than 30% - Reorganize the index If fragmentation(avg_fragmentation_in_percent) is more than 30% - Rebuild index and PageDencity we can caculated based on "avg_page_space_used_in_percent" column but here it is very good and it nearly 80 percent. You have to check for those columns which has page_count is >1000. the avg_fragmentation_in_percent value should be as close to zero as possible , and the avg_fragment_size_in_pages should be as high as possible. And also there are 2 mor...

A session with the status of Sleeping/awaiting command is simply client connection with no active query to the sql server.The table below shows transitions from "running" to "sleeping" states for a session. The question usually around a session that is holding locks and its state is sleeping/Awaiting command.If the client has a open transaction and the client did not submit Commit or Rollback command the state is showing as Sleeping/Awaiting command. We can examine this by running the below query. Open a new query window in AdventureWorks database. And run the below query. Now go to another window run the below query and see the state. It is in Sleeping mode. Giving some more in depth explanation about : A thread is using the CPU (called RUNNING) until it needs to wait for a resource. It then moves to an unordered list of threads that are SUSPENDED. In the meantime, the next thread on the FIFO (first-in-first-out) queue of threads waiting for the ...