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

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


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


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


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


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


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

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


7

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


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


6

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


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

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

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

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


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

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

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

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


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

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

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


4

You're dividing by INT so you'll only ever get a whole number answer. You therefore end up with a rounding problem on your own Space calculations. This is why, when you sum them together, you get a different answer. Although the difference is minimal this is one of those key 'gotchas' with handling non-whole numbers in SQL Server. Change your partition ...


4

You'd have to be polling sys.dm_exec_requests rather frequently, or using trace / extended events to target queries that ended with abort. With the disclaimer that I work for SQL Sentry, Performance Advisor - among many other things - tracks all aborted queries for you, as long as they exceed the Top SQL threshold (5 seconds by default, but that is ...


4

No, there is no knob for clearing just missing_index DMVs. You'll have to restart the service or, if you want to exclude specific tables, you could create a view against the DMVs with a filter. The only DMVs you can clear manually, using DBCC SQLPERF, are sys.dm_os_wait_stats and sys.dm_os_latch_stats: DBCC SQLPERF ( [ LOGSPACE ] | [ ...



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