| Author |
Topic |
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
 Posted - 2011-11-10 : 06:21:05
|
| Doing a big insert from pro to Dev on a table which has 50 million records.How can I find out how many records or how long is left before finishing the insert.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-10 : 06:24:14
|
| how are you doing insert? is it through ssis export/import or are you using t-sql script?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
arkiboys
Master Smack Fu Yak Hacker
1433 Posts |
Posted - 2011-11-10 : 06:25:19
|
| insert into in query analyser.How about sp_spaceused ? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-10 : 10:07:28
|
| nope it gives disk space used------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 11:18:21
|
Dunno if it will work (i.e. whether it will be able to see ANY of the new rows), but:SELECT COUNT(*) FROM MyTargetTable WITH (NOLOCK) ?? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-10 : 13:47:50
|
quote:
Originally posted by Kristen
Dunno if it will work (i.e. whether it will be able to see ANY of the new rows), but:SELECT COUNT(*) FROM MyTargetTable WITH (NOLOCK) ??
better:SELECT rows FROM sys.partitions WHERE object_id = object_id ('YourTable'); |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-10 : 13:50:44
|
| You could also try:SELECT rows FROM sys.partitions WHERE index_id<2 AND object_id=OBJECT_ID('myTable')That will be faster than doing a count on the table. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-10 : 14:19:31
|
| Blimey, is that kept up to date as individual rows are inserted during a batch ?Always amazed me what SQL finds CPU-time available to do behind the scenes! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-11-10 : 15:12:18
|
quote:
Blimey, is that kept up to date as individual rows are inserted during a batch ?
Yeah, there's a lot of neat things you can do with the DMVs.quote:
Always amazed me what SQL finds CPU-time available to do behind the scenes!
You need to watch Dr. DeWitt's PASS keynotes, especially from 2010, where he discusses how query optimization works. You'll be amazed that it can process a query AT ALL considering how much work has to be done. |
|
|
|
|
how long