| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2010-12-20 : 11:59:09
|
GreetingsI am getting some very high logical reads and pages on two of my tables.Table 'tblCustomers'. Scan count 0, logical reads 149017, physical reads 2, read-ahead reads 16564.Table 'tblOrders'. Scan count 0, logical reads 149031, physical reads 0, read-ahead reads 1423. When I do DBCC checktable on these two tables I get the followingtblCustomers 4610000 rows in 217710 pagestblOrders 1982428 rows in 90611 pages How do I go about resolving this issues so I reduce the logical reads and pages? I have clustered indexes on each tables' primary key.ThanksIf you don't have the passion to help people, you have no passion |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-20 : 13:20:00
|
| I have all the possible indexes (per http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx) I have over 17million rows on both tables.Clustered on PKNon-clustered on other fieldsLooks like I am bringing back too many rows. But why is there such high 217710 pages. How can I deallocate some of those pages? How can I find out which are empty? ThanksIf you don't have the passion to help people, you have no passion |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-12-20 : 13:31:03
|
| How log does it take to excute? there are onlye 2 physical reads going on, so that probably means almost all the data is in memory. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-20 : 13:41:04
|
| OK i found one of the problems, it is the front end code that is chugging. running the query in SSMS takes 2-6 seconds execute :( pretty fast I would sayThis is another issue. this database was first created in 2005 and then backed up and then imported into SQL 2008.It is now in simple recovery mode, compatibility 2008 (100)If you don't have the passion to help people, you have no passion |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-12-20 : 13:55:13
|
quote:
This is another issue. this database was first created in 2005 and then backed up and then imported into SQL 2008.
Make sure to run UPDATE STATISTICS on all tables if you haven't already done it. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-20 : 14:05:49
|
thanks robvolk. I ran that command on all the whole database. statistics did changed Table 'tblCustomers'. Scan count 0, logical reads 91905, physical reads 0, read-ahead reads 0.Table 'tblOrders'. Scan count 0, logical reads 91905, physical reads 0, read-ahead reads 0. This is fascinating stuff! Never knew there was so much statistics !If you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-20 : 15:25:18
|
| the query is returning 12211 rowsIf you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-20 : 15:37:30
|
| thanks tara!But what are there so many pages is that ? Is that normal?tblCustomers 4610000 rows in 217710 pagestblOrders 1982428 rows in 90611 pagesIf you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-21 : 04:29:32
|
Also it depends on the size of the row.You need to understand on how your rows are saved in the datapages.Have a look at the simple example below.I created a single table with single column id.By default 2 datapages are assigned to it.if not exists(select * from sys.databases where name='TestRowSize')Create database TestRowSizeGoif not exists(select * from sys.tables where name='t')create table t (id int)Use TestRowSizeGoinsert tselect 1exec sp_spaceused tgoinsert into tselect 2go 591exec sp_spaceused tdbcc ind('TestRowSize',t,-1)insert into tselect 3exec sp_spaceused tdbcc ind('TestRowSize',t,-1)drop table tThe o/p of the first exec sp_spaceused t is belows.name rows reserved data index_size unusedt 1 16 KB 8 KB 8 KB 0 KB So in the above example the total number of rows that can be saved on these two datapages are 592.So when I insert another row with value 3 another datapage is assigned to it.So now when sp_spaceused t is executed the reserved column of the sp_spaceused t will return 24KB.You can see the PageID and the pagefileid which gives the exact location of the address for the pages in the datafiles.The whole scenario will change when the datatype of the column id changes from int to tinyint as tinyint takes less data storage.So even for no of rows greater than 592 inserted in table t when datatype of id is tinyint only 2 datapages are used.PBUH |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-21 : 12:19:14
|
| with that explanation I think I now know which fields are the culprits. We ingest data from external source with some fields set to varchar(max) to accomodate / delimited list. I will break these off into another table and just point to them via FK/PK. I have two of those fields with varcchar(max), that must bloat the pages!thank you very much allIf you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-12-21 : 12:43:13
|
| thank you Tara, thanks to you we all learn DMV , vertical partitioning etc. you are the best!If you don't have the passion to help people, you have no passion |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
high logical reads, high pages