Tag Info

Hot answers tagged

22

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


20

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


18

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


16

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


13

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

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


12

You can use a query like this to calculate read/write rates and latency (though due to my laziness these figures are not in units that match Activity Monitor). SELECT d.name, f.name, f.type_desc, f.physical_name, [read b/ms] = num_of_bytes_read * 1.0/sample_ms, [avg read latency] = (1.0*s.io_stall_read_ms / (COALESCE(NULLIF(s.num_of_reads,0),1))), ...


11

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


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


11

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


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

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


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


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


8

Here is a whitepaper on when auto_update of stats occurs. Here are the salient points vis-a-vis automatic updates to statistics: The table size has gone from 0 to >0 rows (test 1). The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more ...


8

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

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

If DTA recommended a single column statistic, you must have auto create stats turned off? Auto create will build stats on any column used in a predicate, automatically, so generally its a good idea to leave it switched on. Check the status of auto update for this database, which again should usually be on. One of the best uses of DTA is for identifying ...


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

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

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

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


6

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


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


6

There is most definitely a difference between SHOW STATUS; and SHOW GLOBAL STATUS; SHOW GLOBAL STATUS; will give you status variables that have updated since mysqld started for all sessions that are connected or have ever been connected. SHOW STATUS; will give you status variables that have updated within your session. The command can also be expressed as ...


6

At least I think it is possible that the procedure gets a different plan and that way it doesn't touch the rows that have "invalid" data, for example numbers or dates stored as varchar that aren't correct and then conversion logic in the procedure. For example if you're fetching with both date range and customer code, and some of the data for that customer ...



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