New answers tagged

9

...why the huge performance hit from joining to sys.databases? And why is it inconsistent? There's nothing special about joining to sys.databases. The optimizer happens to choose a plan that is inefficient for the first query. Specifically, in this area of the plan: ...the optimizer chooses a nested loops join to SYSDMEXECCACHEDPLANS, presumably ...


7

The SOS_SCHEDULER_YIELD accumulation is just like I suggested on #sqlhelp. Each of those equates to 4ms of CPU time for the query, and they always show zero resource wait time, as there is no resource wait involved (thread yields the processor and goes directly to the bottom of the Runnable Queue on the scheduler). So - this query was churning through CPU ...


1

The easiest way is : using SSMS -- connect to instance --> reports -> standard reports --> General There are other ways as well - How to find Analysis Services Server Version ?.


2

What sort of exec requests don't have a sql handle? (sys.dm_exec_requests) Sql_handle is a hash value which identifies SQL text of the batch/query being submitted to the server. Text can be NULL for encrypted objects so this can cause sql_handle to be NULL


3

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored ...


1

From the docs The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. This dmv is therefore reset for each stored procedure recompile, which can happen, for ...



Top 50 recent answers are included