Statistics In SQL Server
Feb 12, 2002
Can I copy statistics in SQL Server from one environment to another without copying the actual data. For example from production to development. It is possible to copy statistics in other databases, like DB2/UDB, Oracle. Reason is to execute some poor performing SQLs and analyze their execution plan.
Did not find anything on this subject in BOL. Since statistics is stored in the statblob column of sysindexes, I tried updating statblob column of the index and rowcnt columns of table & index to mimic the copy. After my updates to 'TO' table
dbcc show_statistics ( stat_test2, stat_test2_idx)
showed the results that is identical to the statistics of my FROM table.
But when I execute a small SELECT on (FROM) table(which contains the original, required stats) and the (TO) table (where the statistics is now copied), I get 2 different execution plans. This means I am not successful in my attempt to cheat the optimizer.
Is there any more columns to be updated?
Is there another direct way to do this?
Thanks in advance.
Gana.
View 1 Replies
ADVERTISEMENT
Jul 20, 2005
Hello group.I have an issue, which has bothered me for a while now:I'm wondering why the column statistics, which SQL Server wants me tocreate, if I turn off auto-created statistics, are so important to theoptimizer?Example: from Northwind (with auto create stats off), I do the following:SELECT * FROM Customers WHERE Country = 'Sweden'My query plan show a clustered index scan, which is expected - no indexexists for Country. BUT, the query plan also shows, that the optimizer ismissing a statistic on Country, which tells me, that the optimizer wouldbenefit from knowing this.I cannot see why? (and I've been trying for a while now).If I create the missing statistics, nothing happens in the query plan (andwhy should it?). I could understand it, if the optimizer suggested an indexon Country - this would make sense, but if creating the missing index, queryanalyzer creates the statistics with an empty index, which seems to me to beless than usable.I've been thinking long and hard about this, but haven't been able to reacha conclusion :) It has some relevance to my work, because allowing theoptimizer to create missing statistics limits my options for designingindexes (e.g. covering) for some rather wide tables, so I'm thinking why notturn it off altogether. But I would like to know the consequences - hopesomebody has already delved into this, and knows a good explanation.RgdsJesper
View 5 Replies
View Related
Aug 1, 2006
What is the unit of the numbers you get in the Time Statistics-part when running a query in Microsoft SQL Server Management Studio with Client Statistics turned on?
Currently I get mostly 0īs, but if I try and *** up a query on purpose I can get
it up to around 30... Is it milliseconds or som made up number based on clockcycles or... ?
I would also like to know if itīs possible to change the precision.
- Nikolaj
View 3 Replies
View Related
Jan 20, 2006
We are inserting huge amount of data (could be several millions) during a fact upload. Therefore after the upload we recomputed the statistics. In oracle this can be done using the following sql statement:
analyze table <table_name> compute statistics
Do you know if there is any equivalent statement in SQL*Server? it has to run via JDBC like a SQL statement.
Thanks.
View 1 Replies
View Related
Jan 13, 2004
In using ADO to connect to SQL Server, I'm trying to retrieve multiple datasets AND statistics that are usually returned via the OnInfoMessage event. For those that are familiar with SQL Server, I need the results returned by the SET STATISTICS IO ON and SET STATISTICS PROFILE ON options. Anyone had any luck doing this before?
Thanks in advance.
View 4 Replies
View Related
Jan 1, 2008
hi all
I am working on query performance and tuning.
I want to save current statistics for the latter use. do SQL Server provide any utility or command (like exec dbms_stats in ORACLE and OPTDIAG in sybase) to do this?
thanks
Gourav
View 1 Replies
View Related
May 20, 2014
I am working on an existing infrastructure and i do not have liberty to change much right now. I am in a situation where app issues update statistics command quite often. So frequently that sometimes one blocks another. Is there any way i can do something like this
IF ( update_statistics going on)
dont do anything
else
run update statistics
This is temporary solution untill i fix bad inline SQL code (in app) and use SPs.
View 8 Replies
View Related
Jul 23, 2014
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
View 2 Replies
View Related
Feb 5, 2015
Currently our database size is around 350G. It will grow up to 1.5 TB
We have the
Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False
at database level
we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.
Previously they tested with sampling but instead of full scan running with the sampling effected the queries.
Is there option to avoid the long time job duration.
If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases
View 9 Replies
View Related
Aug 19, 2015
I have been asked to create a report for one of our clients. The report is pretty basic but I am concerned about the overheads with my planned approach.The report is at a table and field grain to include values for:
* Min column value
* Max column value
* Number of discrete values
* Number of populated values (not NULL)
My current plan is to have a cursor over a limited view of sys.tables and sys.columns that will run a dynamic SQL query to import the results into a table that I can then output.There must be a better way of doing this and I don't have access to any DQS services.
View 1 Replies
View Related
May 30, 2007
Hi,
I am using SQL Server 2000 sp4 (standard edition); when I run an Update Stats command against a table I get error 3628:
UPDATE STATISTICS IMSV7.BLITEMRT WITH SAMPLE 20 PERCENT
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction is canceled.
Does anyone know of a fix or a workaround?
View 3 Replies
View Related
Jul 16, 2007
I want to be able to reproduce my production execution plans on development with copying data.
View 1 Replies
View Related
Feb 21, 2014
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies
View Related
Dec 23, 2014
I'm working on databases where statistics of some indexes (tables) are changing too frequently. Once I update them manually, one minute after they get 10-20% change, and five minutes after they get over 100% change. Tables get updated very frequently (multiple times in a second).
When I run a query to read from sys.stats, sys.dm_db_stats_properties and other dynamic views, I see that they were last updated when I did it manually, but the change rate overpassed the 500+20% (tables have multiples of 10K rows). Auto create and update statistics are set to true on all databases, and I don't know why sql server does not do that automatically.
View 2 Replies
View Related
Aug 8, 2015
I'm looking for a quick script that someone has already written to update statistics (not to rebuild or re-organise) on specific indices in specific databases - I guess loop though a table comprising of a list of databases and the indices.
I know Ola has one but I'm not look for something that is that complicated. If I cannot find one I'm going to have to write one myself - I want to try and avoid re-inventing the wheel as tomorrow I have to do this work and it's about 7K plus indices in about 10+ databases.
View 2 Replies
View Related
Nov 5, 2015
If I rebuild some indexes that are above 30% of average fragmentation, should I after that update statistics?
Also, How can I see if I Need to update statistics^on the tables of my database?
View 3 Replies
View Related
Jul 23, 2005
Does anyone know how to get SQL Server versions, Service pack versionsand the Server that instance of SQL is running on a network. I haveseen some threads on how to list SQL servers in a network but I need tofind more info about these SQL servers.Any help appreciated..!Thx...
View 1 Replies
View Related
Jul 31, 2000
I need a script to drop all statistics in a database at once.
HELP!
View 5 Replies
View Related
Jun 14, 2001
I want to be able to generate a script that gives me all statistics that are in my database currently.
Does anyone know how to do this? Is the following correct:
select --a.id as SysIndex_id,
'create statistics ' + a.name + ' on ' + b.name + ' (' + SUBSTRING(A.NAME, 9, LEN(A.NAME)-17) + ')'
as SysIndex_name
--b.name
from
sysindexes A left join sysobjects B
on A.id = B.id
where a.name like '%wa%'
order by b.name
View 1 Replies
View Related
Aug 3, 2007
Hi all,
As part of my automagical nightly index maintenance application, I am seeing a fairly regular (3-4 failures out of 5 attempts per week) failure on one particular table in my database. The particular line which seems to be failing is this one:
DBCC SHOWCONTIG (WON_Staging_EPSEst) WITH FAST, TABLERESULTS, ALL_INDEXES
The log reports the following transgression(s):Msg 2767, Sev 16: Could not locate statistics 'WON_Staging_EpsEst' in the system catalogs. [SQLSTATE 42000]
Msg 0, Sev 16: [SQLSTATE 01000]
Msg 0, Sev 16: -------------------- Simple ReIndex for [WON_Staging_EpsEst].[IX_WON_Staging_EpsEst] [SQLSTATE 01000]
Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Msg 0, Sev 16: [SQLSTATE 01000]
Msg 0, Sev 16: -------------------- Post-Maintenance Statistics Report for WON_Staging_EpsEst [SQLSTATE 01000]
Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, WON_Staging_EpsEst [SQLSTATE 01000]
Msg 2528, Sev 16: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Msg 0, Sev 16: Statistics for WON_Staging_EpsEst, IX_WON_Staging_EpsEst [SQLSTATE 01000]
Msg 2768, Sev 16: Statistics for INDEX 'IX_WON_Staging_EpsEst'. [SQLSTATE 01000]
Updated Rows Rows Sampled Steps Density Average key length
-------------------- -------------------- -------------------- ------ ------------------------ ------------------------
Aug 3 2007 3:22AM 674609 674609 196 2.0958368E-4 8.0
(1 rows(s) affected)
This table is dropped and recreated each day during a data import job. After the table is recreated and repopulated with data (using a bulk import from a flat file), the index is also recreated using the following code:CREATE INDEX [IX_WON_Staging_EpsEst]
ON [dbo].[WON_Staging_EpsEst](OSID, [Year], Period)
ON [PRIMARY]Yet more often than not, that evening, when the index maintenance job runs, it fails with the aforepasted messages complaining of being unable to find table/index statistics.
Worth noting, perhaps, is that this same process is used on roughly 10 data staging tables in this database each day, and none of the other tables fail during the index maintenance job.
Also worth noting, perhaps, is that this IDENTICAL table/code is processed in exactly the same way on TWO other servers, and the failure has not occured in any of the jobs on those other two servers (these other two servers are identical mirrors of the one failing, and contain all the same data, indicies, and everything else.
Any thoughts, suggestions for where to look, or unrestrained abusive comments regarding my ancestry?
Thanks!
View 14 Replies
View Related
Jun 13, 2008
I have a small doubt.
If we apply a statistics command on a particular table what will it update.
Normally statistics are created automatically by the server or we have to create it.
View 1 Replies
View Related
Jul 20, 2005
Anybody know how many companies worldwide use SQL server and how manyindividual servers this amounts to? Also, at what rate is SQL usegrowing? Can someone at least point me to a source where I could findclose to exact numbers?
View 2 Replies
View Related
Jun 20, 2001
Here my data sample on which I need to perform some stats
Time(Sec) Result
1 2
2 8
3 6
4 2
5 2
6 4
7 2
8 7
9 8
What I need from this is a result set that looks as follows
GroupNo Value
1 5.33
2 2.67
3 5.67
This is a grouping of the result data in 3's by time. Value is the average of the Group.
In need to write a select statement to do this.
Note the Group could be done from 1 to 10
The end result of this is to display a Range Chart which shows Results grouped according to requirements.
Any Help would nice.
Pargat
View 6 Replies
View Related
Oct 10, 2001
Hello List,
I would like to know, How can I drop Statistics from tables. My user tables has two indexes and and some statistics created onto them. I would like to drop the statistics indexes and apprecaite, If someone please advice.
The statistics indexes looks something like this:
"_WA_Sys_status_01EAB64E"
Any help would be apprecaited.
Thanks,
View 4 Replies
View Related
Jun 6, 2000
What are some ways to analyze index coverage and usage? I have a 18 GB database, half is data, other half is indexes and I want to cut down that number as much as I can without affecting performance. Thanks
Peter Karhatsu
View 2 Replies
View Related
Nov 17, 2000
Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!
View 3 Replies
View Related
Feb 14, 2001
I have been monitoring some indexes on a table with a lot of inserts, no updates and no deletes. I was wanting to determine when to update the statistics on the index. Does anyone know what would be a good target range for the density when you run the dbcc show_statistics?
View 1 Replies
View Related
Jun 14, 2000
When the "create statistics" command is run, what table entries are made into system tables?
I want to check for the existence of statistics on certain columns and if they are not there, create them. What is a good way to see if they are already created?
View 1 Replies
View Related
Feb 6, 2001
I am using SQL 6.5. We have tables of upto 2.5GB in size. Running update statistics againts these tables takes too long, and locks out users.
Anyone know how I can make this quicker?
Davy
View 4 Replies
View Related
Jun 9, 2000
I was wondering how often you should reindex. By looking at dbcc showcontig and statistics I see that I am heavily fragmented and scan density is between 10-30% on my important indexes. I'm thinking of scheduling this to be done nightly. nay help is much appreciated.
Pete Karhatsu
View 4 Replies
View Related
Dec 9, 2004
Hi,
I am contemplating creating a job to execute every 5 mins which will update index statisics if they are more than say 8% out. I would like to know what thoughts people have on this? i.e. pros and cons.
I like forward to what you have to say.
I have auto stats on. Our stats are often more than 10% out. At what level do you reckon the query plan might be effected by out of data stats?
Thanks
Jamie.
View 5 Replies
View Related
Dec 7, 2005
Is the Update Statistics found in a Maintenance Plan performed online or offline? Will it kick users out when this is run on SQL Server 2000?
Thanks
View 1 Replies
View Related
Sep 6, 2006
Hi,
It seems to me there are many ways to update statistics for a table. i.e. "sp_updatestats", "sp_recompile", "dbcc updateusage"
Can somebody tell me the difference between those commands and what's the best way for updating your statistics? Does reindexing update the statistics?
thx,
Wilfred van Dijk
View 3 Replies
View Related
