Hot answers tagged

17

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by: how big your database is how fast your IO subsytem ...


11

As one of the guys writes demo DMV queries that way, I'll explain why. Does it matter if you're only querying DMVs? No. But sooner or later, you're going to take one of your DMV scripts and tack on a join to sys.databases or sys.tables or some other system object in order to get more information about what you're looking at. If you don't have read ...


11

Even though you fixed the immediate rounding issue, the overall algorithm to get per-object / index stats is incorrect. It does not properly handle LOB and row-overflow data. It also excludes: Indexed Views, FullText indexes, XML indexes, and a few other cases. Hence, you might not be seeing all of your data. The following is an adaptation of the code I ...


10

In addition to the recommendation by @Remus to use a SAMPLED scan, I don't know that this query can't start until your maintenance window starts. Why not pre-populate a table with the results? If you start this query (let's say a sampled scan takes 10 minutes) about 15-20 minutes before your maintenance window, and stuff the results in a table, the data ...


10

Yes, that's what it looks like. Unless there is some error happening with the data after it got moved to Excel. However, these are all tiny, tiny tables. Stop caring about fragmentation on tables with less than, say, 1,000 pages.* And even then you probably shouldn't care too much until another order or two of magnitude, and even less if you are using SSD ...


9

Its clearly visible that page_count for all the indexes shown in figure you attached is < 1500. In such case even if index is fragmented to 100% this is NOT going to cause any performance issue. Actually below is recommendation on fragmentation from Microsoft if you read BOL 2000 version Fragmentation affects disk I/O. Therefore, focus on the larger ...


9

Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation: It says: ... one row per cached stored procedure plan. You read: ... one row per cached stored procedure. You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents ...


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


8

Assuming you are talking about data that is encrypted with SQL Server keys, there is way to find these columns. The Key_name() Function will return the name of the key used for the encryption for that particular value and will return NULL if there isn't anything encrypted with a "known" key (3rd party, or simple not encrypted). With that knowlegde we can ...


8

The problem with cell level encryption is that the column itself isn't really encrypted, it's the data contained in that column. The columns themselves are just varbinary columns (because that's what's required) and could contain completely legible data. It's the use of the ENCRYPTBY* and DECRYPTBY* functions that truly make the data encrypted. You can ...


8

Worker time is the time the task(s) was effectively active, occupying a scheduler and running code (ie. not suspended). Elapsed time is clock time. On a DOP 1 query the worker time will be at most the elapsed time, less if the task was suspended at any moment during execution (thus the clock time would advance, but the worker time not). For DOP > 1 the ...


8

use dbx; select foo from db1.dbo.table join db2.dbo.table on condition where some_function(); This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is ...


7

I don't see that it makes any difference. If I try the following and compare the lock output for both isolation levels in winmerge they are exactly the same (and even putting it up to SERIALIZABLE doesn't change the output). /*Do once so compilation and caching out the way*/ EXEC('select st.text, qp.query_plan, cp.cacheobjtype, cp.objtype, cp.plan_handle ...


7

Reads and writes are expressed in terms of "the number 8K pages." It should be documented better on the page you reference, but you can piece this together from other areas of the documentation, e.g. from Reading Pages: A logical read occurs every time the Database Engine requests a page from the buffer cache. If the page is not currently in the buffer ...


7

At the moment this does not seem like a possible value to get, which is unfortunate as this does seem like a handy value to have. Ok, I did find it (mostly). There is a last_execution_time field in the sys.dm_exec_query_stats DMV, though that DMV comes with the following warning: An initial query of sys.dm_exec_query_stats might produce inaccurate ...


7

My first question would be to ask why you're actually using incremental in the first place. Here's an answer that I posted regarding incremental statistics, a blog post by Erin Stellato that illuminates one of the primary complaints and pitfalls with incremental statistics (they're not used at the partition level by the optimizer), and two blog posts by me ...


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


6

Not possible. These DMVs do not offer even self-consistency guarantees. For instance it is possible for a resource to appear in different mutually exclusive states, in the same run, in the same result set. If such DMVs would take the necessary steps to 'stabilize' the data during a scan the results would be disastrous for performance (imagine all locks being ...


6

In terms of the Statement Text being NULL, when selecting the text from sys.dm_exec_sql_text, the text value is NULL for encrypted objects. Check the encrypted value of the record from sys.dm_exec_sql_text.


6

It's really hard to say how long your rebuild will take, as SQL itself doesn't really know in advance and cant give you an estimate. You can use the following query to use the dm_exec_requests dmv to view how long your index rebuild has been going on for, and to verify that SQL doesn't really have an estimate: SELECT ...


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

Disclaimer: These scripts have been tested on SQL Server 2005/2008. However, this code and information are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties or merchantability and/or fitness for a particular purpose. As always, test this in your test environment before ...


5

As long as you are sure the server was up for that entire time, and that nobody cleared out DMV stats inadvertently. This can happen if the database is detached + re-attached / restored / offline + online / auto-close + online, or if the index has been explicitly dropped / re-created (the DMV is not affected by disable / rebuild / reorganize, except in the ...


5

This is the one I would use, courtesy of Kimberly Tripp: SELECT objtype AS [CacheType] , count_big(*) AS [Total Plans] , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs] , avg(usecounts) AS [Avg Use Count] , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1] , sum(CASE ...


5

In SQL Server 2005 and SQL Server 2008 there was no documented way. So undocumented command xp_regread was used to get the result DECLARE @sn NVARCHAR(128); EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', 'ObjectName', @sn OUTPUT; SELECT @sn; Since SQL Server 2008R2 SP1 we have a ...


5

No, this is currently not possible, since the missing index DMVs do not have any facility for identifying opportunities for filtered indexes (and hence you won't see them recommended in showplan output, database engine tuning advisor, etc). These are things you have to identify a little more manually. I don't think the new Azure Index Advisor does this, ...


5

Missing index info will be cleared from the view if the referenced table has changed. Added columns, new indexes would clear out this data. When you remove indexes, any missing index info for that table will also be removed. Index rebuilds also clears out this info, so if you are doing nightly index maintenance, this may contribute to the change in ...


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


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


4

Sessions belong to connections (1:M) and connections have unique GUID identifier, see sys.dm_exec_connections: connection_id uniqueidentifier Identifies each connection uniquely. Is not nullable. Whenever you capture sys.dm_exec_sessions, join with sys.dm_exec_connections and capture the connection_id too.



Only top voted, non community-wiki answers of a minimum length are eligible