[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to add free space to index files?

Posted on 2011-10-27
19
Medium Priority
?
267 Views
Last Modified: 2012-06-27
we have plenty of disk space free, but our index have 0 free space in them. I'd like to add more so they aren't auto-growing so often. I would give them more space than I know they'll use for a long time. Is this possible? or do I just set "autogrowth" to that amount I want it to grow by and once it does, change it back? If I change autogrowth do I need to restart?
0
Comment
Question by:MrVault
  • 10
  • 7
  • 2
19 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1668 total points
ID: 37037697
you can add more files to the index filegroup, or indeed change the size of the existing files.
do you want to do this via the GUI or the T-SQL ?
0
 

Author Comment

by:MrVault
ID: 37037705
GUI is fine. t-sql is cool to learn if you want to show that too.
0
 

Author Comment

by:MrVault
ID: 37037713
sorry, I didn't mean add more index files for the same index. I'm not sure how that works or if it helps. I was thinking just give the file for that index more space. how does it work with multiple files? it will be on the same raid spindle set.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37037874
not sure, but you might want to first introduce yourself to know what a filegroup is in sql server:
http://msdn.microsoft.com/en-us/library/ms179316.aspx
possibly you know already, I just have the impression you don't or that possibly english is not your native language ...

so, did you create several filegroups in your database?
and if yes, did you create the table and the index into different filegroups?
if still yes, you should actually know that you can add more files to the "index" database ...
0
 

Author Comment

by:MrVault
ID: 37037961
I'm laughing here because English is my first and only language. I'm in such a hurry today, multitasking that my writing seems short hand. Anyway, I know what a filegroup is and we created 5. One for the main mdf file, one for the clustered index and one for each of the non-clustered indexes. Each filegroup has a single file in it. We did this so we could see which of the NCI's has the highest IO needs and put that on spindles that can provide it while putting the others on less needy sets. This has worked well for us across many other duplicates of the same setup. In this instance though the avg disk sec/transfer is high (as is Avg Disk Queue length). we are taking measures to help these with faster disks, more of them, bigger cache, plus database tweaks. But in the meantime I know that having to constantly auto-grow can degrade performance. I have heard about having multiple files for an index, but have not done that before. If the extra file is going to go on the same set of disks out of necessity, does that still help the matter? Also, does it matter that the data in the index file changes almost every 10 to 14 days completely due to the nature of our product?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1668 total points
ID: 37038585
>I know that having to constantly auto-grow can degrade performance.
it will degrade performance mainly during the time it actually has to execute the growing of the data file(s).

>If the extra file is going to go on the same set of disks out of necessity, does that still help the matter?
only by having more space in total, not by having more files necessarily

>Also, does it matter that the data in the index file changes almost every 10 to 14 days completely due to the nature of our product?
no

note: you could split your index "filegroup" into several files, each file on a different set of spindles, eventually sharing the one for the data files, hence sharing the high load on the index over the different disk systems ...
0
 

Author Comment

by:MrVault
ID: 37039682
how does having extra files lead to more space in total?

I don't have enough spindles to dedicate to each file.

so basically the question becomes: Is there any benefit to splitting an index into multiple files if they will be on the san LUN and set of physical disk?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1668 total points
ID: 37040225
there is an advantage: speed of backup and restore: smaller files means faster backup and restore.
for the rest, if you have the disks on LUN, it does not really help to have several files in first place, but it won't be bad to split the "hot spots" among several files.
0
 

Author Comment

by:MrVault
ID: 37040257
thanks. so the total backup time and restore time of the same index in 2 files will be shorter than 1 file? what type of magnitude we talking about relatively? I'm aware it depends on size, and hardware performance, etc, but we talking about 5% faster or 25% faster? An article to link to would be great.

Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37040320
in regards to the performance gains: I have no real idea, and found no quick article.
but I do know that if I have a big database with just 1 database file, say 1TB, the backup will run on that 1 single file for say 1 hour.
if the db is split into 5 database files of each 200GB, the backup can run 5 threads in parallel, which can then run a bit more than 1/5th of the hour.
especially with a SAN behind, your read performance for the backup will not be limited to a single spindle, so you can do more or less that math.
given the overhead still of several files, and the write performance being the bottleneck usually on the destination disks, but still you could then expect it to be done in 15 minutes instead of 60 ...

side note on considerations for filegroups usage:
http://www.sql-server-performance.com/2006/filegroups/
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 332 total points
ID: 37040335
>> it will degrade performance mainly during the time it actually has to execute the growing of the data file(s). <<

I have to take some exception to that.  If you have IFI (instant file initialization) on -- which you should -- the time to actually add space to the file is minimal.

You will take a much bigger hit from fragmentation of the file on disk.  This is especially true if you have a small growth amount (like, yikes, only 1M).  One large *contiguous* block of disk is much better performing than many smaller fragments of disk totalling the same amount, especially when scanning a (very) large table.

[I once had a table that prior to defragmenting the physical file (not the whole disk, just the db file) took 45 mins (!) to scan, but after defrag the table took 1.5 min to scan (still quite a while in SQL terms, but it was a *huge* (history) table).]
0
 

Author Comment

by:MrVault
ID: 37040429
thanks for that article link angellll. I saw the part where it says reads will also be faster as it can read across multiple files. Here's a very important question though:

How do I measure the impact in performance? How do I see what the most intensive queries are in our database (by total calls, total duration across all, duration for a single one, cpu time), measure their current time to run so that I can measure the impact of splitting it into multiple files? right now we have 12 x 600GB 15K SAS drives. We originally created 6 raid1 sets and then tried  3 raid10 sets and gave a third of highest IO indexes to each raid set. turns out some are more intense than others so 4 disks was not enough for that index. we tried 8 and 4, etc but couldn't find a good setup. finally we created a single large 12 disk raid10 set and the average seconds per read or write dropped dramatically. so right now that's where we're at. so my question becomes, if we have multiple high IO indexes (with 1 or 2 being higher significantly than the others), how do we know how many files we should split the index filegroup file into? 2, 4, 12? no offense to you, but there are a ton of articles out there proclaiming how to speed up my sql server, but very few that seem to provided step by step on how to measure if it's actually getting better. I'm a jack of all trades, so I don't have the time do become a seasoned DBA right now. I still have to manage AD, DNS, FIREWALLS, VPN, SAN, SWITCHES, WORKSTATIONS, and PEOPLE across multiple sites. I'd love extra help, but no money for it. Anyway, enough complaining :)
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1668 total points
ID: 37040476
"measuring" can be done in a couple of ways, but there are basically 2 indicators:
* IO and CPU measures on the server before and after the changes (and it looks like you did that already using the performance tracing tools of windows server ...)
* performance of the end-user applications before and after the changes (this is usually trickier, unless you have a simulator application ...)

OPTIMAL would be to have a dedicated test environment, copy of the prod, together with simulating applications submitting the same "traffic" as in real-life ... oh, wait ... you said "no money" ... welcome in real world ... ;(

0
 

Author Comment

by:MrVault
ID: 37040517
I thought sql tracks performance stats of queries somewhere that can be analyzed. like top run queries, top queries by time, etc
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37040627
Of course the tricky part of using multiple files in a filegroup is that you don't control what SQL puts into each file.  

That is, AFAIK, you can only tell SQL to put a table into a certain file*group*, you *can't* tell SQL to put parts of the same table into different *files*.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1668 total points
ID: 37041031
0
 

Author Comment

by:MrVault
ID: 37063546
That's it. Thanks!
0
 

Assisted Solution

by:MrVault
MrVault earned 0 total points
ID: 37063566
The solution for the original question was to change the initial size of the file in the filegroup (which I did via GUI, but I'm sure there's a T-SQL command for it), then to restart the services.
0
 

Author Closing Comment

by:MrVault
ID: 37089752
The solution for the original question was to change the initial size of the file in the filegroup (which I did via GUI, but I'm sure there's a T-SQL command for it), then to restart the services.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question