Tunning T-SQL ??????
Apr 23, 2008
Hi
I m going through tunning part and fired query on table Code having 12500 rows
SET STATISTICS IO ON
GO
SELECT COUNT(*) FROM Code
GO
SET STATISTICS IO OFF
GO
sp_spaceused Code
got results as
Table 'Code'. Scan count 1, logical reads 60, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Name: Code
rows : 12500
reserved: 7424 KB
data : 6272 KB
index_size: 1008 KB
unused : 144 KB
If we creat table from select * into table syntax and then create PK on the table then why there is difference in results of index_size?
got results as
Table 'TestCode'. Scan count 1, logical reads 394, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Name: TestCode
rows : 12500
reserved: 3208 KB
data : 3136 KB
index_size: 16 KB
unused : 56 KB
here really index not playing part???
help me out
T.i.A
View 2 Replies
Aug 10, 2003
Hi, Friends!
My problem is: Ms SQL 2000, Service Pack 2 (we should install Service Pack3), 1G RAM, database not so big- about 600M.
We have an application of our client running on it.After 15-20 minutes RAM reach the limit.
So what I can do in DB configuration to fix the problem without fixing SQL or stored procedures in application.
I'm new in Ms SQL, so every advance will be appreciated!!!!
Marina.
View 1 Replies
View Related
Jan 18, 2007
This started on a Monday, SQL Server is straining the Server box,
I tried checking up on all the stored procs and changes that happened the previous week onto the Database, but nothing really major, So I tried archiving most of the data that's not needed,
Took down the MDF file from 1.2 GB to 633MB, and switched off the auto growth function on the Data File and Log File,
But the Server is still hammered,
I read a few articles on using the Index Tunning Up Wizard, but still
Curious if its safe,
Can anybody suggest anything, before i make my rough changes
View 1 Replies
View Related
Mar 7, 2008
hi,
pls help me,tel abt query tunning & performance tunning.
View 10 Replies
View Related
Apr 8, 2008
Hi,
Somebody please help me with mostly asked question on Performance Tunning Section. I have my client interview schedule for this profile.
Thanks in advance.
-- Chetan
View 3 Replies
View Related
Apr 19, 2008
Hi,
Is there any index tunning tools for MS SQL 2005 better than its own one?
Thanks,
View 2 Replies
View Related
Jun 18, 2008
Dear All,
I am having the customer db and which is running since last 1995. it is having the simple customer table which record all the customer who is visiting our 4 star hotel. this table is having 19,23,499 records. i want to run before that it was running within 1 min to display result. but suddunly it was stop down. how to tune and performe to get much faster or prevent in feature..............
View 2 Replies
View Related
Jun 27, 2007
In our environment we have a very high insert intensive OLTP table, this has 60 million rows. From some of our applications we are very rarely getting timeouts when inserting data into this table. We are expecting that this table would soon run into billion rows and continue to grow. below is the table description and indexes. What indexes should we keep/remove on this table?
Column Name Datatype computed Length Precision Scale Nullable
Col1 int no 4 10 0 no
Col2 smallint no 2 5 0 no
Col3 smallint no 2 5 0 yes
Col4 int no 4 10 0 yes
Col5 int no 4 10 0 yes
Col6 int no 4 10 0 yes
Col7 char no 10 yes
Col8 int no 4 10 0 yes
Col9 uniqueidentifier no 16 yes
Col10 datetime no 8 yes
Col11 smallint no 2 5 0 yes
Col12 varchar no 15 yes
Col13 varchar no 30 yes
Col14 varchar no 50 yes
Col15 int no 4 10 0 yes
Col16 int no 4 10 0 yes
Col17 varchar no 12 yes
Col18 smallint no 2 5 0 yes
Col19 smallint no 2 5 0 yes
Col20 int no 4 10 0 yes
Col21 bit no 1 no
Col22 int no 4 10 0 yes
Col23 bit no 1 no
Col24 bit no 1 no
Col25 datetime no 8 no
Col26 char no 6 yes
Col27 char no 6 yes
Col28 smallint no 2 5 0 yes
Col29 char no 2 yes
Col30 int no 4 10 0 yes
Col31 smallint no 2 5 0 yes
Col32 smallint no 2 5 0 yes
Col33 bit no 1 yes
Index1 nonclustered, stats no recompute located on FileGroup2 Col9, Col10, Col12
Index2 nonclustered, stats no recompute located on FileGroup2 Col9, Col12, Col11, Col10, Col4
Index3 nonclustered, stats no recompute located on FileGroup2 Col6
Index4 nonclustered, stats no recompute located on FileGroup2 Col6, Col4
Index5 nonclustered, stats no recompute located on FileGroup2 Col5, Col9
Index6 nonclustered, stats no recompute located on FileGroup2 Col4, Col9
Index7 nonclustered, stats no recompute located on FileGroup2 Col29
Index8 nonclustered, stats no recompute located on FileGroup2 Col9
Index9 nonclustered, stats no recompute located on FileGroup2 Col6, Col9
Index10 nonclustered, stats no recompute located on FileGroup2 Col7
Index11 nonclustered, stats no recompute located on FileGroup2 Col12
Index12 clustered, unique, primary key, stats no recompute located on FileGroup3 Col1
Any links to MS best practises on indexes for sqlserver 2000 appreciated.
thanks a lot in advance.
View 6 Replies
View Related