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


9

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

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


6

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


6

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


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


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


3

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


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

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

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


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

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


2

Both DMVs are a snapshot of what is happening at the precise moment they are executed. It is therefore highly likely on a busy system that running SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions; SELECT session_id, transaction_id FROM sys.dm_tran_session_transactions; could display differing result sets.


2

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


2

One approach that I like to use, is to focus just on the most frequently executed queries and not all queries that might benefit from having indexes. The reason for this is the impact and cost (CPU time, I/O) that additional indexes would have on DML (DATA MODIFICATION LANGUAGE) statements. Imagine a table customers that has 4 columns (id_customer, ...


2

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


1

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


1

In SSAS, there's no such thing as a table; it's all multi-dimensional stuff. What you need to do is get the results into a table in a database engine instance. Here's one way to do it: Create a linked server to SSAS on your management SQL Server database engine instance. (Note: you'll need to install the SSAS drivers if the two instances aren't on the ...


1

If anyone's curious, I ended up doing this. It gives me something, but I'm not really convinced that the data is good enough to be better than no data. -- Set up table -- Put this somewhere other than TempDB if you want it to persist across restarts USE TempDB DROP TABLE Sessions CREATE TABLE Sessions (last_request_end_time datetime, login_name ...


1

Other than the naming scheme (and consulting the docs - e.g. sys.sysfilegroups has a big yellow warning) there isn't much you can do to tell. You would hope for some flag in sys.all_objects or sys.all_views but nothing differentiates them.


1

This doesn't exactly answer your question, but Paul Randal included a very helpful query for aggregating wait stats with one of his surveys last December. I have shamelessly copied and pasted it here, but am providing a link to his site, because there is a wealth of knowledge to be had from it. Wait Stats WITH Waits AS (SELECT wait_type, ...



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