| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2011-06-15 : 17:38:00
|
| I have a table with over six million rows that takes up about 750 GB of drive space, and it's growing fast. The table only has 7 columns but one of them is varbinary(max) because we use it to store files (XML, PDF, etc.), and each file is usually about 1 to 3 MB. Insertions into this table (single rows) are taking between 4 and 30 seconds, and the ones that are 20 seconds and up are very problematic for my company's applications. There's only one index and it's the clustered primary key, which only has around 8.6% fragmentation with a fill factor of 100. Is that enough fragmentation to cause problems? The database has 4 mdf files on separate disks, but they're all in the primary file group. What can I do to diagnose and/or speed up insertions? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-15 : 17:54:37
|
| Have you considered using filestream? From what you've described you might benefit from it: http://technet.microsoft.com/en-us/library/bb933993.aspxThere's also the FileTable feature in the next SQL Server version: http://msdn.microsoft.com/en-us/library/ff929068(v=sql.110).aspx |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-06-15 : 17:55:52
|
| How do you recommend looking at the IO subsystem? What counters should I use in PerfMon? Will reorganizing the index cause locking while it's running?Thanks. |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-06-15 : 17:58:16
|
| I've thought about using filestream but I don't have a good server to test it on and I have no idea how long it would take to convert the table to filestream. Is there a way to convert as an online operation? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-15 : 18:14:33
|
| If filestream isn't an option, you could try creating a separate filegroup for documents and create your table with TEXTIMAGE ON [TextFileGroup]. Unfortunately that is not an online operation, in fact you have to create the table from scratch. It may not make a big improvement in inserting documents, but any queries that exclude that column should be much faster. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-06-16 : 05:41:38
|
| There are a couple of other things you can consider:- Have you verified that this is not a network problem? If the client is on a wireless network and they're inserting 3MB files or more the data transfer alone will take some time. - Do the database files have enough free space to accommodate the file uploads? File growth in sql server has a tendency to take a lot of time so this should be done in a controlled manner out of office hours, preferably in large chunks- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2011-06-16 : 20:25:48
|
| Thanks all. The server is running great today under a similar load, so I'm not sure what the deal is. File insertions are taking less than 3 seconds right now.Tara: I'm assuming you mean "Avg Disk sec/Write" and "Avg Disk sec/Read." If so, I'm seeing averages below .003, but spikes above .03 on occasion. I even saw a few spikes above 2.0.Rob: I like the TEXTIMAGE_ON suggestion, if the problems come back I'll see if I can find enough downtime to try it.Lumbago: If it were a network problem wouldn't the Duration trace in Profiler ignore the network time and just show the time processing the insert? Free space/file growth is not an issue, but we don't have off hours anyway, we are a 24/7 company. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Slow BLOB Inserts (Not Bulk)