April 8, 2014 at 6:39 am
What is the use of create statistics ?
I am having a DB With AUTO_CREATE and AUTO_UPDATE for statistics ON, then why i again need to create statictics manually? what does this actually doing?
USE tempdb
GO
-- Clean up objects from any previous runs.
IF object_id(N'Contact','U') IS NOT NULL
DROP TABLE Contact
GO
CREATE TABLE Contact(
FirstName nvarchar(80),
LastName nvarchar(80),
Phone nvarchar(20),
Title nvarchar(20)
)
GO
-- Populate the table with a few rows.
INSERT INTO Contact
VALUES(N'Tester',N'Testing',N'5345345',N'Mr')
INSERT INTO Contact
VALUES(N'Unit',N'Units',N'35345',N'Mr')
INSERT INTO Contact
VALUES(N'Meter',N'Meters',N'3535',N'Mr')
INSERT INTO Contact
VALUES(N'Volt',N'Volts',N'85673',N'Dr')
INSERT INTO Business.Contact
VALUES(N'Gun',N'Guns',N'850234',N'Mr')
GO
-- Observer that there are no statistics yet on the Business.Contact table
sp_helpstats N'Contact', 'ALL'
GO
-- Implicitly create statistics on LastName when you run the below query
SELECT * FROM Contact WHERE LastName = N'Testing'
GO
-- Observe that statistics were automatically created on LastName.
sp_helpstats N'Contact', 'ALL'
GO
-- Now Create an index, which also creates statistics for the index
CREATE NONCLUSTERED INDEX IDXNC_Phone on Contact(Phone)
GO
-- Observe that creating the index created an associated statistics object for the index
sp_helpstats N'Contact', 'ALL'
GO
from the above it is saying that for each where caluse a statistics is created
then what is update statistic which is TRUE doing why we need to create statistic?
April 8, 2014 at 6:45 am
Automatic create stats will only create single column stats. There are cases (rare ones) where you may want multi-column stats. Or you may want to turn off auto create and create your own stats, not usually recommended though.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2014 at 7:15 am
Why i am asking this is while running the profiler i got some tables saying that
_dta_index_TABLE1_9_117575457__K4_K30_K1_K6_K9_K21_K16_K13_K12_K17_3_7_8_10_11_14_15_18_19_20_22_23_24_25_26_27_28_29
SELECT M.* , COL1, COL2, COL3,COL9, COL4, COL5, COL6,COL7,COL8,COL10,COL12,COL11,COL13 FROM TABLE1 M (NOLOCK), TABLE2 OM (NOLOCK) WHERE (M.COL1 = OM.COL1)AND (M.COL3 = '{69BA108D-4549-4ED9-84DC-2585D27B1185}') ORDER BY M.COL4, M.COL7, M.COL19, M.COL14,M.COL11 DESC,M.COL10, M.COL15 DESC,COL6,COL2,COL4,COL5
April 8, 2014 at 7:18 am
Someone ran the Database Tuning Advisor against your database and probably accepted all recommendations without doing the testing which they should have done.
But, that's not a statistics object (what I thought you were asking about), that's an index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2014 at 7:25 am
GilaMonster (4/8/2014)
Someone ran the Database Tuning Advisor against your database and probably accepted all recommendations without doing the testing which they should have done.But, that's not a statistics object (what I thought you were asking about), that's an index.
Originally my question was on that one only.
Later when i came across these thing had doubts how these statistic are working in DTA.
Here comes another question, if the recommended index relationship in DTA shows this informations, should not we run the statistics?
April 8, 2014 at 7:39 am
yuvipoy (4/8/2014)
Originally my question was on that one only.Later when i came across these thing had doubts how these statistic are working in DTA.
Here comes another question, if the recommended index relationship in DTA shows this informations, should not we run the statistics?
All recommendations from the DTA are just that, recommendations. It's up to you to validate whether or not those recommendations are useful on your system.
One good use for manually created statistics is using filtered statistics with partitioned data. Just so you know.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2014 at 8:11 am
yuvipoy (4/8/2014)
Here comes another question, if the recommended index relationship in DTA shows this informations, should not we run the statistics?
You're mixing up statistics and indexes. They are two very different things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 8, 2014 at 11:17 am
GilaMonster (4/8/2014)
You're mixing up statistics and indexes. They are two very different things.
what is DTA Statistics ?
And what is DB With AUTO_CREATE and AUTO_UPDATE for statistics ON?
April 8, 2014 at 11:23 am
AUTO_CREATE means that when a query is run against the database and there are no statistics for a column in a WHERE clause, a JOIN, or other areas where statistics are needed, a set of statistics starting with _WA_ are created automatically. AUTO_UPDATE means that on a regular basis (defined here) will automatically update the statistics as defined by those rules on the database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 8, 2014 at 11:54 am
Grant.! Good Point to know.
What about DTA statistics ?
are they not statistics?
How DTA able to do get those details(Statistics), which means that it was missed by Auto Update Statistics?
April 8, 2014 at 12:12 pm
yuvipoy (4/8/2014)
Grant.! Good Point to know.What about DTA statistics ?
are they not statistics?
How DTA able to do get those details(Statistics), which means that it was missed by Auto Update Statistics?
There is no such thing as "DTA statistics." There are statistics suggested by the DTA tool. If you take those suggestions and run the create scripts provided by the DTA, then those are now statistics, just like any other. The only thing that makes them stand out is the naming convention. That's why Gail was able to spot them so quickly. The DTA takes the workload or query you provide it and then attempts to make statistics or indexes that it thinks will improve the query. So, the statistics aren't based on the need of the optimizer which AUTO_CREATE fulfills. Instead it's suggestions from whatever math goes on inside the DTA to determine possible places for improvement. In my opinion, and I think Gail shares it, the DTA suggestions are mostly junk. However, if you apply them, AUTO_UPDATE_STATISTICS applies to them in the same way it applies to any other statistic because they are no different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 9, 2014 at 12:12 pm
Grant Fritchey (4/8/2014)
yuvipoy (4/8/2014)
Grant.! Good Point to know.What about DTA statistics ?
are they not statistics?
How DTA able to do get those details(Statistics), which means that it was missed by Auto Update Statistics?
There is no such thing as "DTA statistics." There are statistics suggested by the DTA tool. If you take those suggestions and run the create scripts provided by the DTA, then those are now statistics, just like any other. The only thing that makes them stand out is the naming convention. That's why Gail was able to spot them so quickly.
And it's not even statistics. The item whose name was listed earlier is an index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply