New answers tagged

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 ...


6

There is a bug that aggregates the time in a parallel operation. This is fixed in 2014. The total_elapsed_time will be correct for a particular parallel query in a batch until it moves on to the next statement in the batch, then the total_elapsed_time will explode by the DOP. Example Run this in one query window: USE AdventureWorks2012 GO SELECT * FROM ...


5

It looks like it could also be an bug/issue with the DMV. There is a Connect bug report here of this same kind of inaccuracy. The suggested workaround is to use GETDATE() - sys.dm_exec_requests.start_time instead of total_elapsed_time. The issue is resolved in SQL Server 2014. To quote the comment on the Connect item by "Nathan (MSFT)": The issue ...


0

I've checked a couple servers and on the background requests observed a drift of about 14s over 2 months. However that aside, the only significant difference I can see on other requests is where the spid has gone into a SLEEPING state. I suspect that this value does not increment while in that state; but I haven't been able to force a query into SLEEPING in ...


0

Neal Hambly in his post at SQL Server SOS_SCHEDULER_YIELD Wait Type includes this comment (highlights are mine): If you have long running queries, especially when in conjunction with high signal wait times, it may be an indication of CPU pressure. Reviewing the system performance will show if the CPUs are showing signs of CPU pressure, and ...


0

The code below will find all varbinary columns in a database that have been encrypted using a symmetric key present in SQL and is a little more efficient than some of the other solutions. CREATE TABLE #EncryptedColumns ( schema_id INT NOT NULL , object_id INT NOT NULL , column_id INT NOT NULL , schemaname SYSNAME NOT NULL ...


5

You can't reset the DMVs, however you can work around this limitation and remove rows from the DMVs by creating a small filtered index on the tables mentioned in the DMVs then immediately dropping that index. For instance: CREATE INDEX IX_temp ON dbo.SomeTable(SomeKey) WHERE SomeKey IS NULL; DROP INDEX dbo.SomeTable.IX_temp; I've created a script to ...


3

If you connect SQL Server Profiler to SSAS and watch the Progress Report Current events with an EventSubclass=ReadData the IntegerData column will show the cumulative rows read so far for that SSAS partition. Integerdata: Contains the integer data associated with the reported event, such as the current count of the number of rows processed for a ...



Top 50 recent answers are included