Tag Info

Hot answers tagged

10

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


10

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


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

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


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


6

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


5

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


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

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.


4

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


4

SQL Server doesn't really track database access in the way you want, at least going backward (you can set up things like server-side trace, extended events, auditing, etc. going forward). There is one ballpark thing you can use: DMVs that track index usage and procedure/trigger/query stats. For example: ;WITH d AS ( SELECT d = database_id, name FROM ...


4

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


4

The following two queries give index usage stats per table which should be a good indicator of which tables are causing the most IO operations. SELECT d.name as [Database], OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES FROM ...


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

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


3

sys.dm_tran_session_transactions is an intermediate view allowing to join the DMV's that are primarily identified on session_id column - sys.dm_exec* DMV's with other sys.dm_tran_* DMV's. This view will tell if the transaction is a user transaction is_user_transaction = 1 or a system transaction with is_user_transaction = 0. On the other hand, ...


3

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions: -- No result SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions; Using an explicit transaction does return results: BEGIN TRANSACTION; -- Row returned SELECT session_id, transaction_id FROM ...


3

Both the Plan Cache and 'optimize for adhoc workloads' are per instance.


3

There is no inconsistency: 1048576 * 8 = 8388608 KB => 8 GB; Both Perfmon and sys.dm_os_sys_info show 8 GB; [bpool_visible], [bpool_commit_target] and [bpool_committed] - in SQL 2005 all these are defined as "Number of 8-KB buffers ..." (http://msdn.microsoft.com/en-us/library/ms175048(v=sql.90).aspx), so you have to multiply the result by 8 to get ...


3

The clustered index has 140992 pages, from which only 1410 are page compressed. Page compression is attempted only when it fills up, see When Page Compression Occurs: When a new table is created that has page compression, no compression occurs. However, the metadata for the table indicates that page compression should be used. As data is added to the ...


3

You are in the wrong database context. Try: select avg_fragmentation_in_percent from databasename.sys.dm_db_index_physical_stats(DB_ID('databasename'), OBJECT_ID('tablename'), 1, NULL, 'limited') Alternatively you can just throw a USE Databasename on the first line. I'm guessing that it's executing in the context of master or something similar, ...


3

Almost always, backward compatibility. Often for end user compatibility but also for internal purposes. I'd swear I was reading an article earlier this week that mentioned one of the old views, possibly sysfiles, having been 99% removed during SQL2005 development but the final 1% proved to difficult/risky to eliminate. As a general rule, target the most ...


3

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.


3

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


3

sys.dm_exec_query_stats returns aggregated stats for plans, but with a row per query. So if a procedure has 2 queries, then sys.dm_exec_query_stats would return 2 rows, but the plan-related columns (such as compile time, execution count, etc) are plan level stats, and cannot be further aggregated.


3

There are limitations when using sys.dm_exec_query_plan 5.4 When and How to Use Sys.dm_exec_text_query_plan The query_plan column returned by sys.dm_exec_query_plan has type XML and suffers from the inherent limitation of the data type that it cannot have nest levels greater than or equal to 128. In SQL Server 2005 RTM and SP1, if the query_plan had a ...


2

Number of pages read and written. For the most part everything is reported in pages, unless something else is specified.


2

I tried to reproduce what you're experiencing and couldn't make it happen, unfortunately (see the second part of this answer). If this was a bugfix between versions, it was pretty well undocumented: running this through Google produced absolutely nothing. I suspect the problem is that the analysis query you're running is joining sys.dm_db_index_usage_stats ...


2

If your server has been up for a reliable amount of time (e.g. at least one business cycle), you can tell when there has been any read or write against any table in a database using DMVs like sys.dm_db_index_usage_stats. There isn't really a reliable way to tell who last "used" a database. SQL Server doesn't log this information unless they do something ...



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