Tag Info

Hot answers tagged

19

Statistics simply are a form of dynamic metadata that assists the query optimizer in making better decisions. For example, if there are only a dozen rows in a table, then there's no point going to an index to do a lookup; you will always be better off doing a full table scan. But if that same table grows to a million rows, then you will probably be better ...


16

Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of ...


16

Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution. Therefore, CREATE INDEX will be the DDL to create an index (clustered, nonclustered, etc.) and CREATE STATISTICS is the DDL to create the statistics on columns within the table. I recommend you read about these ...


14

Consider the simple AdventureWorks query and execution plan shown below. The query contains predicates connected with AND. The optimizer's cardinality estimate is 41,211 rows: -- Estimate 41,211 rows SELECT COUNT_BIG(*) FROM Production.TransactionHistory AS TH WHERE TH.TransactionID BETWEEN 100000 AND 168336 AND TH.TransactionDate BETWEEN ...


12

The buffer pool is a cache of the database. There is never an 'or', things that are in the buffer pool are also in the database, always. And anything read from the database must be, even temporarily, present in the buffer pool. As for the question: statistics are in the database so a backup/restore will preserve the statistics. Note though that ...


12

You can keep the following in mind when caring about updating statistics (copied from Rebuilding Indexes vs. Updating Statistics (Benjamin Nevarez) By default, the UPDATE STATISTICS statement uses only a sample of records of the table. Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table. By default, the UPDATE STATISTICS statement updates ...


11

This is a big "It Depends..." but generally speaking you don't want to have statistics updating asynchronously for most workloads because it can mean that you generate a less-than ideal execution plan based on the existing out-of-date statistics. If you have a scenario where the auto update stats execution takes an excessive amount of time and causes ...


10

I have come up with the following: select cume, max(var) AS max_var from ( select var , ntile(5) over (order by var) as cume from table ) as tmp group by cume order by cume; It selects the maximum of each group that is divided using ntile().


10

If you don't have the maintenance window for it, updating statistics daily is probably a little overkill. Especially if you have Auto Update Statistics turned on for the database. In your original post, you said that users are seeing a performance degredation due to this maintenance plan. Is there no other time to run this maintenance plan? No other ...


10

Addition to what Remus has mentioned, I would suggest you read -- SQL Server Statistics Questions We Were Too Shy to Ask Aarons answer to - Where are Statistics physically stored in SQL Server? UNDERSTANDING SQL SERVER STATISTICS


10

Are multicolumn histograms possible? Not true multi-dimensional histograms, no. Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns? SQL Server does support "multi-column" statistics, but they only capture average density ...


9

When To Update Statistics? if and only if auto update statistics feature is not good enough for your requirements. i mean if auto create and auto update statistics are ON and you are getting a bad query plan because the statistics are not accurate or current then it might be a good idea to have control over statistics creation and update. but if you are ...


8

You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance: select db_name(st.dbid) as database_name, object_name(st.objectid) as name, p.size_in_bytes / 1024 as size_in_kb, p.usecounts, st.text from sys.dm_exec_cached_plans p cross apply sys.dm_exec_sql_text(p.plan_handle) st where ...


7

Nothing so easy. You would need to use DBCC SHOW_STATISTICS and look at the histogram, taking into account that the statistics might be filtered or multi column. Probably easier to just let SQL Server do it and generate an estimated execution plan for the statement select * from foo where bar is null SQL Server will then use appropriate statistics if they ...


7

Between sys.stats and DBCC SHOW_STATISTICS you have all the info needed to see the statistics of your statistics. Eg.: set nocount on; declare @object nvarchar(256), @stat sysname; declare crs cursor forward_only read_only static for select quotename(object_schema_name(object_id)) + '.' +quotename(object_name(object_id)), name from sys.stats; ...


7

For WITH SAMPLE 50 PERCENT it works as though for each data page in the table SQL Server flips a coin. If it lands heads then it reads all the rows on the page. If it lands tails then it reads none. Tracing the UPDATE STATISTICS T WITH SAMPLE 50 PERCENT call in Profiler shows the following query is emitted SELECT StatMan([SC0], [SB0000]) FROM (SELECT ...


7

You can grab this data with the Auto Stats Event Class with SQL Trace. One of the event's data columns is Duration. As per the referenced BOL documentation above, here is a quote: Duration: Amount of time (in microseconds) taken by the event. That should give you a pretty good idea when/how long auto stats is taking. If you are seeing a lot of ...


7

That's a big 'it depends.' Depending on how your statistics have been maintained and the options you specify you could end up running full table/index scans and thrashing your I/O and buffer pool. Depending on the characteristics of your hardware and databases that could be very bad. Also, rebuilding statistics invalidates execution plans, which means you ...


7

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior? The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of ...


6

Parallel statistics update has been available since SQL Server 2005. It is documented in the TechNet article, "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005": Where a full scan is performed (whether explicitly requested or not) the internal query generated for the data-gathering has the general form: SELECT StatMan([SC0]) FROM ...


6

Your select on dba_tables doesn't take into account: Empty blocks in the table (see initial/next, minextents and freelist-related storage parameters among others) Empty space in the blocks (due to pct_free mainly) Block headers (initrans influences this size, among others) I.e. it doesn't take into account the physical storage of the data at all. ...


6

By default, the script does not perform statistics maintenance. The documentation for the script's parameters can be found here. Value Description ================================ ALL Update index and column statistics. INDEX Update index statistics. COLUMNS Update column statistics. NULL Do not perform statistics maintenance. This is the ...


5

They are used by the query optimiser (whitepaper on MSDN) to track distribution of values in indexes and/or columns. Your only concern should be to update regularly: just leave the DB engine to do its stuff


5

Its the sub selects in your column selection that is causing the slow return. You should try using your sub-selects in left joins, or use a derived table as I have defined below. Using Left Joins to two instances of Third Table SELECT TempTable.Col1, TempTable.Col2, TempTable.Col3, JoinedTable.Col1, JoinedTable.Col2, ThirdTable.Col1 AS ...


5

statistics is used when query execution plan created. Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The query optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality ...


5

I don't know what CREATE STATISTICS does, but statistics for the optimizer are collected using the ANALZYE command when autovacuum is running - which is turned on by default. Statistics are always collected for all columns, no need to turn it on specifically. You can control the level of details collected for the statistics on a per-column basis using ...


5

It's because when you rebuild indexes, you also necessarily rebuild the statistics on each index as it happens. That means that SQL Server rebuilds internal "tables" of data about the distribution of data in your indexes themselves. The distribution of data indicated by your statistics govern how the cost-based query optimizer chooses to create plans for ...


5

A couple more (superficial) reasons: Auto-update stats will block the query that triggered the update until the new statistics are ready. ...Unless you also enable auto-update stats asynchronously. Then the query that triggered the update won't wait for the new stats, but will potentially run with the old, incorrect stats. I also ran into some strange ...


5

You are running RTM (10.50.1600.1): the latest patch is SP2 + CU3 (10.50.4266) These include patches like KB 2498786



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