May 27, 2008 at 5:26 am
IS IT POSSIBLE TO ANALYZING ABOUT RAID , FILEGROUP IN DATABASE ... I AM NOT AWARE OF THAT ... SO ONLY ASKING ???
May 27, 2008 at 5:32 am
The simple query you posted has a simple index fix, and Barry gave it to you on page 1 of this thread
http://www.sqlservercentral.com/Forums/FindPost506666.aspx
Other queries, especially if they have lots of columns will be more difficult.
Is there a column that you always join on? If so, consider making that the clustered index.
honestly, considering the amount of data you're retrieving and the size of the table, I don't think you can get guarenteed index seeks. The key/RID lookups will be just too expensive.
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
May 27, 2008 at 5:53 am
barry solution :
CREATE [UNIQUE] INDEX idxTablenameKeyDT
ON TableName ( Key, DT )
with only column KEY and selected a column in multiple place .. for that and all i cannot create like this index .. minimum i have 15 places like this but different select list .also i am doing BCP and deletion in this table .. do u say is it correct way to create unique index for all select list column ????
May 27, 2008 at 5:57 am
niranjankumar_k (5/27/2008)
do u say is it correct way to create unique index for all select list column ????
No. I said that was a solution to the small query you posted and that other queries, especially if they had larger select lists wuld be more difficult. Since that's the only query I've seen, it's the only one I can suggest anything for.
Index doesn't have to be unique. [] when used in a command description means that the contents is optional.
Create the indexes after the BCP befre you stat querying the table.
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
May 27, 2008 at 10:23 pm
if i create index after BCP , further i do deletion and updation .. so it will reduce performance due to cluster index creation .
May 27, 2008 at 10:53 pm
1030 columns... that's a bit wide. I'm thinking that I'd divide the columns into logical groups and do a little horizontal partitioning with some decent indexing. Might even evaluate the table for nulls and other repeating data in columns which is a sure sign that something needs to be normalized.
Last but not least... you do realize that you'll never be able to replicate such a table, eh? Replication in 2k has some limit near 600 columns or so.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2008 at 11:29 pm
if i replicate the table i have to do modify the whole application .. that is hard ... in one sp i have one select qry . this sp executed from batfile . which is scheduled every week . select list contains 1000 columns , this takes 3 hrs ... for this engle qry can i do horzontal partioning ??? statiscal report is
STATISTICS REPORT :
> >>=================
> >> Table 'A1'. Scan count 8, logical reads 160572, physical reads 0,
> >> read-ahead reads 128208.
> >>
> >> Table 'A2'. Scan count 8, logical reads 305858, physical reads 3,
> >> read-ahead reads 320673.
could you say what each term (logical reads ,physical reads , read-ahead reads ) says .. and how to calculate the space ...
how many pages a table has .. each page contains how many rows ???
May 28, 2008 at 12:04 am
niranjankumar_k (5/27/2008)
if i create index after BCP , further i do deletion and updation .. so it will reduce performance due to cluster index creation .
Have you tried it?
Unless you have a large number of indexes, insert/update/delete performance doesn't degrade badly. Update and delete may even improve if the index supports the query.
could you say what each term (logical reads ,physical reads , read-ahead reads ) says
Look it up. It's in Books Online.
how many pages a table has .. each page contains how many rows ???
Depends on the size of the table, depends of the size of the rows. A page is 8k.
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
May 28, 2008 at 12:54 am
ok no issue i try this ...
before i explain something in my process ..
BCP happens in both table (A1,A2) .. A1 ( key column ) has identity ... A2 does not have .. so with another 3 column ( created non cluster ) A2 key column transfered from A1 .
Join can be done with one cluster column and another one is non cluster ???
space :
============
Table rows reserved data index unused
A1 434 4304 KB 4040 KB 184 KB 80 KB
A2 434 2184 KB 2024 KB 112 KB 48 KB
ecery week 3.5 to 4.5 lak will inserted . before insertion previous week data will be truncated .. after this insertion some of invalid data between 1 to 1.5 records get deleted .... there is no other index .. only 2 index with 3 columns each i have created ...
DB_SIZE
---------------------
File_size usedspace_MB UNusedspace_MB DBFilename
------------------------------------------
169976.00 143608.75 26367.25 SCHEMA_Data ------------------------------------------
10366.75 66.45 10300.30 SCHEMA_Log
------------------------------------------
can i create CI now ???
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply