Index Statistics And A Primary Key
Sep 5, 2007
Hi
I have a question regarding updating statistics for a primary key.
Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.
Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?
Hope someone can help
Thanks
James
View 1 Replies
ADVERTISEMENT
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
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 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
Jan 13, 1999
At managing index SQL Server 6.5, there is distribution button. I have a database on production server, one of the table has 20 indexes. When I press the Distribution button, it reveals that most of the indexes have very poor selection of index, from 30% to 99%. There are 6 of them are very good. Based on this statistics, do you think I should remove these indexes? As the book says, if the statistics is higher than 5%, the optimizer will not use index but do a table scan. Removing those index should not affect the performance, is that right?
Your suggestion is very appreciated.
View 1 Replies
View Related
Nov 28, 2006
I ran the DBCC SHOW_STATISTICS command for all of my indexes; I was told that high density numbers are bad, low numbers good. I have some questions about my results, though; I'm not sure how to interpret them.
Of 48 indexes, 14 have a density of 0. Does this mean that the indexes are not selective enough? Does it mean they're garbage and I should toss them?
6 have a density of NULL. They are all primary keys. I suppose this just means that they're never used because these tables are rarely queried. Would this assumption be correct?
13 have a density of 1. I have no idea what this means.
The others have densities ranging from 0.01210491 to 0.5841165. I was told that the lower this number is, the more selective and thus more useful an index is. I think 0.5841165 is too high a number. Would this be correct?
Thanks in advance.
View 14 Replies
View Related
Feb 26, 2008
Is it neccessary to schedule a update statistics on index in sql server 2005 on daily basis
Is it neccessary to schedule a rebuild index on index in sql server 2005 on daily basis
View 9 Replies
View Related
Sep 7, 2004
Hello!
Is there any way to determine index usage statistics for a given table?
For examle, I have a table, with three indices. I need to know how many times each index was used. Is it possible?
And second part of question: I need to know, which user overloads my base with their giantic queries. Is there any way to determine, how many system resources each of user's sessions uses?
MS SQL Server 2000 Enterprise Edition.
Thank you!
View 3 Replies
View Related
Jul 30, 2007
We have a 20 GB database and reorganize indexes and update statistics maintainance takes about 4 hours and the log files grows out of control what is a serious problem since it can not be truncated (database mirroring).
Ivan
View 4 Replies
View Related
Dec 12, 2006
Hi ..
Please advice whether i have to do update statistics along with Index rebuild (online /off line)
Thanks in advance
View 3 Replies
View Related
Jan 15, 1999
I have an index that shows distribution statistics of 98.20%, which is very poor. I set show query plan and show statis I/O on. This table has 1113675 rows of data.
*************
select orderID, custId, intertcsi from tblorders
where intertcsi = '2815'
STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Index : indxInterTCSI
orderID custId intertcsi
----------- ----------- ---------
1015245 1011313 2815
2556392 2556392 2815
....
Table: tblOrders scan count 1, logical reads: 104, physical reads: 58, read ahead reads: 0
***************
Then I use the same select statement to force a table scan:
select orderID, custId, intertcsi from tblorders (index=0)
where intertcsi = '2815'
STEP 1
The type of query is SELECT
FROM TABLE
tblorders
Nested iteration
Table Scan
orderID custId intertcsi
----------- ----------- ---------
60472 61084 2815
102184 102333 2815
...
Table: tblOrders scan count 1, logical reads: 110795, physical reads: 6891, read ahead reads: 103980
When the index is not provided, the logical reads and physical reads increased dramatically. Does this tell me that I should keep that index though it is a poor selection? Is that because a huge table like this make the optimizer use the index. The query without using index takes longer time to run.
Any idea or comment would be very appreciated.
View 4 Replies
View Related
Nov 21, 2014
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
View 2 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 3, 2015
At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
View 4 Replies
View Related
Sep 5, 2015
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id
int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id stats_name
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id index_name
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc
[Code] .....
View 6 Replies
View Related
Jun 1, 2008
Hello there,I got a problem when I'm trying to Index a table with PrimaryKeyMy code so far:1 'Create my table2 nonqueryCommand.CommandText = "CREATE TABLE UpdateHistory (id integer IDENTITY(1,1) NOT NULL, version varchar(50) NOT NULL)"3 Console.WriteLine(nonqueryCommand.CommandText)4 Session("Tables") = Session("Tables") + "Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"5 6 'Set column id to Primary Key7 nonqueryCommand.CommandText = "CREATE INDEX idxid ON UpdateHistory (id) With PRIMARY"8 Console.WriteLine(nonqueryCommand.CommandText)9 Session("Tables") = Session("Tables") + "PrimaryKEY - Number of Rows Affected with table UpdateHistory is: " + nonqueryCommand.ExecuteNonQuery().ToString + "<br />"
I do get this error:System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'PRIMARY'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at updates_100_2.Page_Init(Object sender, EventArgs e) in http://server//admin/NewSystem/1.0.0/2.aspx.vb:line 163Note: Line 163 is 9 here..
Anyone who can see what I'm doing wrong?
View 1 Replies
View Related
May 17, 1999
Running SQL 6.5, using ActiveX controls(ADO and Datagrid),NT 4.0. All SP's
are current.
I lose the ability to get distinct values with a client-side cursor
after I set up a primary key. Select distinct works perfect before the
primary key creates an index. Any ideas?
View 1 Replies
View Related
Dec 9, 2004
By defining a numeric field in table as primary key, will the table be indexed on that particular field?
View 14 Replies
View Related
Apr 1, 2004
Hi,
I have a table in my SQL 2000 database called utContact, this has a primary called ContactID (int, idenitity), by default this should have a clustered index on it, but when I go to view the indexes on the table it shows the primary key index as non clustered.
How can this be?
SQL server also will not let me change the index to clustered, but I need to as this is causing a lot of table scans in query execution plans.
Any help or advice on this matter would be greatly appreciated.
Cheers
View 7 Replies
View Related
Oct 28, 2006
Is there any way to change the value of a primary key value?
View 10 Replies
View Related
Oct 9, 2007
Hi All,
One of my client having 1 million(nearly) records in a table.
I defined the table as below
1) Created table with one col(we can name it as "ID") having IDENTITY
2) Using "alter table", I created CLUSTERED PRIMARY KEY Constraint on Same field (ID)
3) The Primary key having 2 ref with another 2 tables
Now the issue is when we create or define a primary key (With Clustered Option) automatically cluster Index will be created on defined table
As such table having huge data whenever any updation or insertion against that particular table taking huge amount of time, because the cluster Index trying re-paging whole data. Because of re-paging each and every time "Transaction Log also growing in huge" (database is in full recovery mode and client wants in same mode only)
Data partitioning not posible because whole data related and current live data
I tried following options with vain
1) To Clear transaction log I suggested to take regular log backup's
2) I tried to drop cluster index and tried to implement non clustered index
Drop and re-create index is take taking huge amount of time
Even in this process I have to Re_Index remaining Index's also
Pls give me any other solution or suggestion in this regard
with Thanks & Regards
Bhaskara
View 8 Replies
View Related
Apr 9, 2008
'PXQ_2' table
- Unable to delete index 'PK_PXQ'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]I/O error (bad page ID) detected during read at offset 0x0000000102e000 in file 'D:MSSQL2000MSSQLxxx.mdf'.
I can't drop Primary Key. How to fix this problem?
Hardware(HDD) no error. I test oke?
Please help me.
-= MIB =-
View 7 Replies
View Related
Jan 28, 2008
Dear All,
i've read one article that with some option, we can avoid creating clustered index on the primary key column. is ti possible?
how can we create a primary key without allowing sql server to create automaticaly a clustered index?
Vinod
Even you learn 1%, Learn it with 100% confidence.
View 1 Replies
View Related
Dec 11, 2007
Dear all,
I want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).
Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).
Let me know your views.
Regards,
Chetan
View 3 Replies
View Related
May 2, 2007
I am using RDA to download a tables
I use TrackingOffWithIndexes
the issue is that when I download a small set of data the primary key index is there
when I download a larger set. the index gets dropped (no error) just slowness when querying the table
the max database size is set to 1024mb and the temp database size is set to 1024mb
the actual db size (with the missing index) is 262 mb... lot's of room left!
when I try to create the index manually after the download in sql server management studio I get this error:
Not enough storage is available to complete this operation
I am using 2GB sd cards and nothing else is on the card.
any ideas?
Regards,
eric [at] westgen com
View 14 Replies
View Related
May 30, 2000
I created table and also I define the primary key its okay
but when I generate the SQL script for that table its not
creating the primary key
CREATE TABLE [dbo].[table1] (
[emp_id] [int] NOT NULL ,
[emp_name] [char] (25) NULL ,
[emp_address] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
PS: I want to use emp_id as primary key but its not defined in the sql script
Thankx a lot
View 3 Replies
View Related
Apr 22, 2003
indid =1 works, is this the correct way
View 3 Replies
View Related
Feb 23, 2006
my table :
CREATE TABLE [dbo].[users] (
[ID] [int] NOT NULL ,
[A1] [nvarchar] (100) NULL ,
[A2] [nvarchar] (100) NULL ,
[A3] [nvarchar] (100) NULL
) ON [PRIMARY]
i must keep ID columns as primary key
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
but now A1+A2 must be unique
how can i do it ?
thank you
View 5 Replies
View Related
Dec 17, 2006
for MS SQL 2000
how can I do this in one time (into the CREATE TABLE)
CREATE TABLE [dbo].[Users] (
[id_Users] [int] NOT NULL ,
[Name] [nvarchar] (100) NULL,
[Serial] [nvarchar] (100) NULL,
) ON [PRIMARY]
ALTER TABLE [dbo].[Users] WITH NOCHECK ADD
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[id_Users]
) ON [PRIMARY]
CREATE UNIQUE INDEX [IX_Users] ON [Users]([Serial]) ON [PRIMARY]
and that one
CREATE TABLE [dbo].[UsersExtra] (
[id_Users] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[UsersExtra] ADD
CONSTRAINT [FK_UsersExtra_Users] FOREIGN KEY
(
[id_Users]
) REFERENCES [Users] (
[id_Users]
) ON DELETE CASCADE
thank you
View 6 Replies
View Related
Aug 10, 2007
Hallo,
Following scenario:
Table1 with column: Year char(4), Quarter char(1), ID decimal(10,0), Hits int
PK_table1 on Year,Quarter,Hits
If i do a "Select Year From table1 group by Year", the executionplan looks like
select year from dbo.table1 group by year110NULLNULL1NULL4NULLNULLNULL86,21644NULLNULLSELECT0NULL
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))121Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL406E-061186,21644[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams, ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))132ParallelismGather StreamsORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL800,028547491186,21643[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Stream Aggregate(GROUP BY:([Testdb].[dbo].[Table1].[YEAR]))143Stream AggregateAggregateGROUP BY:([Testdb].[dbo].[Table1].[YEAR])NULL803,6248431186,18788[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Clustered Index Scan(OBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD)154Clustered Index ScanClustered Index ScanOBJECT:([Testdb].[dbo].[Table1].[PK_Table1]), ORDERED FORWARD[Testdb].[dbo].[Table1].[YEAR]1,449936E+0774,588317,9747291182,56304[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Now, with another index IX_Hits on hits and the same sql query, sql server now takes IX_Hits instead of PK_table1. And, it takes more time. Any idea why?
select Year from dbo.table1 group by Year110NULLNULL1NULL4NULLNULLNULL85,54985NULLNULLSELECT0NULL
|--Sort(DISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC))121SortDistinct SortDISTINCT ORDER BY:([Testdb].[dbo].[Table1].[YEAR] ASC)NULL40,011261260,0001374511185,54985[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW01
|--Parallelism(Gather Streams)132ParallelismGather StreamsNULLNULL800,028507491185,53845[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Hash Match(Partial Aggregate, HASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR]))143Hash MatchPartial AggregateHASH:([Testdb].[dbo].[Table1].[YEAR]), RESIDUAL:([Testdb].[dbo].[Table1].[YEAR] = [Testdb].[dbo].[Table1].[YEAR])NULL8049,63581185,50995[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
|--Index Scan(OBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits]))154Index ScanIndex ScanOBJECT:([Testdb].[dbo].[Table1].[IX_Table1_Hits])[Testdb].[dbo].[Table1].[YEAR]1,449936E+0727,899427,9747291135,87415[Testdb].[dbo].[Table1].[YEAR]NULLPLAN_ROW11
Thank You!!
View 6 Replies
View Related
Apr 15, 2015
Is index created on only primary key or it can be created on secondary key ?
View 2 Replies
View Related
Jul 23, 2005
Is that possible on SQL Server 2000 and onwards?
View 1 Replies
View Related
Jul 18, 2007
Hi,
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO
The records that i added are:
Id Code
1 a
2 b
3 aa
4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a
3 aa
2 b
4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a
2 b
3 aa
4 bb
as per the primary key order that is a non clustered index.
Can anyone explain why it is happening?
Thanks
Nitin
View 3 Replies
View Related
