| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-07-16 : 08:18:57
|
| Hi experts,We have SQL Server 2005 installed in MS Windows server 2003 with 8 GB RAM. This server has 4 processors.Ours is a VLDB and a single table has 400 million records occupying nearly 40 GB of space. We find it vert difficult to meet the response time set by the clients in many occasions. Should the RAM be atleast as big as the biggest table in the database ? Is this mandatory ?Even any other suggestions for improving the performance are welcome.Thanks & Regards,Hariarul |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-16 : 08:31:53
|
| partition the data.have fast disks in RAID 10.have good indexes._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-07-16 : 08:36:26
|
| Thanks Spirit.When the table is partitioned based on a column, it would be good only if the column on which the table is partitioned should be used in the WHERE clause and in JOINs ? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-16 : 08:40:18
|
| i think it would be better to partition the table horizontally_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-16 : 11:36:26
|
| 2005 has very good partition support. You can index individual partitions.Few things to consider:Is tha the only table in the DB or you were just mentioning that as an example? How often do you reindex the table? Do you have auto-update stats set to on? Is there any particular offending stored proc that is taking longer slowing down the system? Do you have anything else installed on the DB server?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-16 : 11:50:23
|
| And don't forget...How are the queries written that access the tables?Any CURSORs?Peter LarssonHelsingborg, Sweden |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2007-07-16 : 17:40:06
|
| Can you post the table structure, the indexes, and what main query(s) your run against this table? That may give us some clues to help tune your query and help you partition your table.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of Microsoft Corp. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-07-17 : 07:55:58
|
| Thank you all for your responses. I have give the information you have asked for.#1 The size of the database is nearly 100 GB.#2 Only 2 tables are huge , TableA with 25 million records and TableB with 400 million records.#3 TableB alone occupies around 40 GB space.#4 PK of the TableA is FK in TableB. Major queries have joins with this key.#5 Other tables are small with not more than 2 million records.#6 DBCC DBREINDEX for the TableB makes the SQL server stand still. CPU goes to 100% & no new connections allowed. The page file is fully utilised. Is this because the table is bigger than the virtual memory (8GB).#7 There are few other non clustered indexes on TableA & TableB.#8 Not much cursors used.Any suggestions would be appreciated.Thanks. |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-07-17 : 07:57:38
|
| Dinakar ,How often should I reindex the table? Should I have the auto-update stats set to on? Thanks. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-17 : 08:16:30
|
| Are there some datetime column in the tableA? If so, partition the tables into two. One with most recent data (say three months) and one table with all the other "history" data.Make an indexed view with the JOIN for the tableA and tableB binding for recent data.Make an indexed view with the JOIN for the tableA and tableB binding for history data.Make an view that combines the two indexed views.Peter LarssonHelsingborg, Sweden |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-07-17 : 08:29:10
|
| Peso,If I use the datatime column to partition as you have mentioned.Should I use the column in all queries to make sure the scan happens on the partitions alone and not the entire table ?If I go for SQL 2005 partitioning , still should I used indexed views ?Thanks.Hariarul |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-17 : 08:34:00
|
| SQL Server is smart enough to choose which underlying indexed views to select from when using the top view in the query.Peter LarssonHelsingborg, Sweden |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-17 : 11:53:54
|
| Although itis transparent to the application, Partitioning can be tricky to complex task and shoul dbe done carefully. We have tables with over 500 mil to over bill rows (250gb) and they work fine (most of the time). We reindex every weekend during a scheduled downtime. Having auto-update stats depends on your environment. There are pros and cons of it. We have it turned off. But yours doesnt seem to be that large of an environment comparatively. Do you know any procs that query these tables and perform badly? Check their query plans and see if the columns being queried on have indexes? and check the query plan to see if the right indexes are being used.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-07-18 : 04:33:32
|
| Thanks Dinakar.In your database (500 mil to over bill rows (250gb)) , have you implemented partitions ? In our database , the auto update statistics is set to True by default. How will this effect the queries? Do you know any procs that query these tables and perform badly? Check their query plans and see if the columns being queried on have indexes? and check the query plan to see if the right indexes are being used.Yeah, we have found some queries like that and started tuning them. They have many operations on table variables. Do you mean that even the columns in the SELECT clause should be indexed ( or covered ) ? |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-18 : 12:14:00
|
quote:
Originally posted by Hariarul
Thanks Dinakar.In your database (500 mil to over bill rows (250gb)) , have you implemented partitions ?
Nope. We were looking into it. Since there is no built in partition in SQL Server 2000 we were looking at taking this as a project when there was another project to start looking into 2005. quote:
Originally posted by HariarulIn our database , the auto update statistics is set to True by default. How will this effect the queries?
We have it set to OFF. Although leaving this setting to ON is a benefit, on OLTP DB's with very high transaction volume it could be an issue. SQL Server decides when to update the stats based on the amount of change in the data. Cant remember off the top of my head but there is some algorithm I belive for when it starts updating the stats. We want SQL Server to just give us the data we need and not add any overhead. Obviously, if we are not letting SQL Server update the stats we have to do it manually ourselves right. Otherwise it will be an even bigger disaster with bad query plans. so we run create indexes jobs followed by Update Stats jobs during our scheduled maintenance window.quote:
Originally posted by HariarulDo you know any procs that query these tables and perform badly? Check their query plans and see if the columns being queried on have indexes? and check the query plan to see if the right indexes are being used.Yeah, we have found some queries like that and started tuning them. They have many operations on table variables. Do you mean that even the columns in the SELECT clause should be indexed ( or covered ) ?
If the SELECT list is not comprehensice as in not every column from the table is in the list, its better to at least "cover" them. SQL Server 2005 has provided an option for this. You can use an INCLUDE clause in the index script and include additional columns in the index. This proved to be very beneficial in our load tests. Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-18 : 16:49:55
|
quote:
Originally posted by Hariarul
#8 Not much cursors used.
Are any of the offending queries using cursors though? Not using them much will be significant if the offending processes use them.Try indexdefrag rather than dbreindex. Not as comprehnesive but similarly will not lock up your database quite so badly. Do you not have slow periods where you can run defrags? Also - check out Tara's scripts on her blog - they are a good example of selective reindexing.Is it selects, inserts or updates that are problematic?Also - could you post the DDL and DML as requested by Michael? |
|
|
|
|
VLDB performance.