Hot answers tagged

25

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


19

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 r.session_id,r.command,CONVERT(NUMERIC(...


17

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


16

This information -- run-time parameter values passed into a Stored Procedure (i.e. RPC call) or parameterized query -- is only available via a SQL Trace (and I assume the equivalent Extended Event in the newer versions of SQL Server). You can see this by running SQL Server Profiler (it comes with SQL Server) and selecting the various "Completed" events, such ...


14

For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing query_thread_profile (and be on at least SQL Server 2016 SP1) but then can use WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') select query_plan.query('//...


13

You can turn on the actual execution plan and then look at the execution plan XML. Or you can use sql sentry's plan explorer tool and see the parameters tab that will list the compiled value and run time value for actual execution plan. If you cannot turn on the actual plan then you can look into plan cache as described below. -- borrowed from Erland ...


13

Microsoft says that DMV (ring buffers) doesn't work on SQL Server 2017, only 2019: Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL Server by built-in tools as well as third party monitoring utilities. When SQL Server 2017 was released on Linux, unfortunately this DMV did not return correct CPU usage information by SQL Server ...


12

Short answer: you can't with 100% accuracy. Long answer: you can query the plan cache to identify plans with missing index warnings and compare the results with what you find in sys.dm_db_missing_index_* DMVs. Here's a script that you could use to query the plan cache. If the plan doesn't get cached or gets pushed out the cache for any reason, you won't ...


12

The reason that the size_in_bytes field of the sys.dm_exec_cached_plans DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize attribute of the QueryPlan node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to ...


12

From the documentation: Note: For per-second counters, this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used. If you'd like something that already does interval sampling, sp_BlitzFirst is ...


12

In reality, this DMV (Dynamic Management View) won't be very helpful for a DBA or Developer and is geared to SQL server Product Support and Product Group. You can ignore this DMV. Hash tables work well when there are a small number of items that fit into a bucket and this is an example of a DMV that support might use to check certain internal structures ...


11

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


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

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


10

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


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

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


10

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


9

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


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


9

According to Docs: In SQL Server the wait-time counters are bigint values and therefore are not as prone to counter rollover as the equivalent counters in earlier versions of SQL Server. I've seen this with earlier version of SQL Server where rollover happened and you'd get negative numbers because the values are signed. I took a look at the source ...


9

What you are looking for is the column query_hash, which was introduced in SQL Server 2008. You can find this in sys.dm_exec_query_stats. Here's a sample query to look at top 20 most common patterns: WITH agg AS ( SELECT TOP 20 COUNT(*) AS similar_query_count, query_hash FROM sys.dm_exec_query_stats qs GROUP BY qs.query_hash ORDER BY similar_query_count ...


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

Because the metadata functions do not obey transaction isolation semantics. If you want to avoid getting blocked, join to sys.schemas and sys.objects instead of using the metadata functions. This will also allow you to set the isolation level in a single statement instead of peppering NOLOCK hints all over the query... This was reported by Adam Machanic on ...


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

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


8

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


8

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


8

Sessions can exists without an active request, but still block other sessions. Consider if you have one window open in SSMS where you run this: BEGIN TRANSACTION INSERT INTO dbo.SomeTable DEFAULT VALUES; Then in another window you run: BEGIN TRANSACTION SELECT * FROM dbo.SomeTable; The first session will be holding locks on dbo.SomeTable, without it ...


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


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