| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-10-09 : 05:31:42
|
| I have a large database, and performance is starting suffer and it gets bigger.This is an off-the-shelf application, so queries cannot be modified.I would like to test it's performance after splitting the primary filegroup across several files.What's the best way to do this? Should I create a new database with this many filegroups and then restore a bak from the original into the new?Or should I use Alter database Add File...If I use the later, at what stage does data start to get inserted into the new filegroups.ThanksDrewDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 06:00:41
|
| Have you got some extra spindles / channels to put the new files on?Otherwise it isn't going to help performance, is it?(Not that I know anything about multiple file groups I hasten to add!)Kristen |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-09 : 06:09:48
|
| Well, apparently (I don't know much either), it says here that while there are definite benefits to puting the seperate files on seperate drives, you can still gain performance if you split the primary group into seperate files on one physical drive. The idea being that if a table is spread across several of those files, SQL Server will use more than 1 thread to access it and in turn potentially be faster. I mean, I don't mind testing it out of curiousity, however I'm not sure what the best way is to go about it, as in the post above.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 07:01:58
|
"SQL Server will use more than 1 thread to access it"Makes sense, my immediately thought was "more head travel for multiple files" but I nothing nuffink!I remember reading something about the first file having to fill up, and then the overspill goes into the second, but I'm blowed if I can remember if it was in this context or a different one, and I've never been confronted with it, as yet.Put the crappy big tables in a separate database and set it to READ-ONLY, that should solve the user's problem Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-09 : 07:07:02
|
| OK! You're my mentor! Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-09 : 08:58:45
|
| I think it may not be possible to do.I created a new database with loads of files, and restored a bak from the old database into it. All the files disappeared bar the original in the old database.If I alter the old database and add new files to it, I am guessing they will remain empty. which means I would have to move half the objects from the primary file to the new ones. Which I'm not going to do.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 09:33:21
|
| "Which I'm not going to do"Hahahahahaha!You could script that part you know?!! |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-09 : 09:43:30
|
| Just spoke to the vendors and they wont support the app if I move the objects.They suggested I trace queries taking longer than 20 sec and above 20000 reads.Then add non clustered indexes to those tables.What do they mean by reads? Reads from the disk? Is this a filter in profiler?Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 09:51:08
|
| That's the Top and Bottom of their Support? Seesh!"What do they mean by reads? Reads from the disk? Is this a filter in profiler?"There's a "long duration template" in SQL Profiler. That will probably get you what you want. But it will include the big queries that run once a month and take hours, of course.What you are after is frequently occurring queries which also take quite a bit of time.Best to put the SQL Profiler results into a Databas4e + Table so you can query the SQL Syntax issues using "LIKE" and then you can probably start doing some ORDER BY to get the frequent occurring, poorly performing, queries.Anyways, that's what I do! (I tend to have a column with a "Query ID" in it so that all the ad hoc ones which are basically variations of the same query get UPDATEd to have the same QueryID, and then I do my stats. based on that)Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-09 : 09:52:55
|
| ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 09:55:43
|
You know about using stuff like-- Comment in one of these:-- SET SHOWPLAN_TEXT ONGO-- SET STATISTICS IO ON; SET STATISTICS TIME ON-- ... put query here - e.g.:SELECT * FROM Northwind.dbo.ProductsSET STATISTICS IO OFF; SET STATISTICS TIME OFFGOSET SHOWPLAN_TEXT OFFGO to see what's-what I presume?Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-10 : 11:21:33
|
| I do now. But have been using the GUI.... one thing at a time :).I have questions and would appreciate your comments.The users complain that the database has been getting slower over time.I traced an ad-hoc query that turned out to have many many joins. I then viewed the estimated execution plan and found 2 of those tables had table-scans (instead of index-scans) performed on them.1. The first question here is, does this categorically mean that these two tables had no clustered index?I then used a script to view the size of each of these two tables. One had 150k rows, the other had 80k.I then used the Index Tuning Wizard/DB Engine Tuning Advisor to suggest on what indexes should be created based on the workload of this one query. I implemented the changes and the query dropped from 20 seconds to less than a second. Running another execution plan showed that SQL Server was now running a Clustered Index seek on those two tables and not a table scan.2. It would be nice NOT to rely on the wizards to suggest indexes, however if the queries were not developed in-house and with there being no stored-procedures for the whole database, is this possible? There are hundreds of tables, how can I possibly optimise these without knowing how queries are using them.Infact, even the wizard requires a "workload" file or table. I couldn't possibly trace every possible query the application makes.So, the question is, how do I know what to index?ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-10-10 : 11:25:32
|
| Oh, here's another question.In addition to creating indexes the wizard created Stats. Quite a few. Before I allowed it to create these stats, I ran Update Statistics on those two tables. I then ran the wizard again, but it still suggested creating new stats?What stats was it refering to then?Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 13:35:18
|
1. This will tell you:sp_helpindex 'MyTable'2. I would use SQL Profiler, store the output to a Database Table (NOT a file), and then analyse.Basically you are looking for the worst performing queries, weighted by how frequent they are. No point busting a gut to optimise a very slow query if it only runs once a month, but OTOH optimising a query from 20ms down to 10ms is worth it if it runs millions of times a day ...If the application uses dynamic SQL your problem will be further compounded by trying to identify queries which are basically the same but have "variations". I add a column to the Results and "classify" each type of query that I can find so that the variations are all grouped as a single classification code.Then I start running the queries (using BEGIN TRANS ... ROLLBACK if they might alter anything) with the SET stuff above and looking at the Query plan and the Logical I/O and Scans. Then I try and index and see if it reduces it ...Its not to hard to remember which indexes you are messing with, so you are likely to realise that you can re-visit an earlier index and add a column to assist a subsequent query - whereas the Wizard may be inclined to make an additional, almost identical, index  Sounds like the Wizard is creating some Stats on things that are NOT indexed. Do you have AUTO STATS turned OFF? I would have expected the optimiser to have created those. I imagine Update Stats will only rebuild the ones that are there, it won't guess what might be useful!Kristen |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-10-10 : 15:42:51
|
| Actually I had the same thing a year or so agoI did it and proved there code was crap and rewrote it to produce the same results from a different dbminutes to microsecondsCursors, sheeshMy VP, was happy and took pleasure in reaming the vendor for the bs they were shovelingBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-10 : 15:48:23
|
| ... and Vendor offered you MegaBuck$, you jumped ship, and lived happily ever after.P.S. All my pigs are fed, and ready to fly! |
|
|
|
|
Splitting a database across many filegroups