Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florence
Could it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>
|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message[vbcol=seagreen]
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
it
>
>.
>
|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> of isolation levels?
> the next time your
> its acceptable by your
> take that long, so you
> cache when the app
> Lite, free!
> in message
> it
Showing posts with label hang. Show all posts
Showing posts with label hang. Show all posts
Wednesday, March 7, 2012
application retrieve same records, sometimes fast, sometimes extreme slow
Hi,
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >
Sometimes, in my application, when i browse a
record, it takes very fast 1/2 seconds but sometimes it
seems like hang there and take a very long time around
10 minutes to finish,could it be deadlock ? Any other
suggestion?
How to check and prevent from deadlock?
thanks.
regards,
florenceCould it be possible you are blocked due to your choice of isolation levels?
You could run sp_who and see if anyone is blocking you the next time your
app slows down. You could set to READ UNCOMMITTED if its acceptable by your
app. Then again, a commit by another app should not take that long, so you
might want to look at fixing the other app instead.
Another possibility is that the data was in the buffer cache when the app
was fast.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> Hi,
> Sometimes, in my application, when i browse a
> record, it takes very fast 1/2 seconds but sometimes it
> seems like hang there and take a very long time around
> 10 minutes to finish,could it be deadlock ? Any other
> suggestion?
> How to check and prevent from deadlock?
> thanks.
> regards,
> florence
>|||Hi,
Thanks for your reply. I didn't set any isolation
level in my application, so i think by default it should
be read uncommitted right? How to detect whether the data
is in the buffer cache but the application run too fast?
Thanks
>--Original Message--
>Could it be possible you are blocked due to your choice
of isolation levels?
>You could run sp_who and see if anyone is blocking you
the next time your
>app slows down. You could set to READ UNCOMMITTED if
its acceptable by your
>app. Then again, a commit by another app should not
take that long, so you
>might want to look at fixing the other app instead.
>Another possibility is that the data was in the buffer
cache when the app
>was fast.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup
Lite, free!
>
>"florencelee" <florencelee@.visualsolutions.com.my> wrote
in message
>news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
>> Hi,
>> Sometimes, in my application, when i browse a
>> record, it takes very fast 1/2 seconds but sometimes
it
>> seems like hang there and take a very long time around
>> 10 minutes to finish,could it be deadlock ? Any other
>> suggestion?
>> How to check and prevent from deadlock?
>> thanks.
>> regards,
>> florence
>
>.
>|||DBCC MEMUSAGE(names, 50) to show the top 50 objects in the buffer cache by
number of pages (each page is 8k).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:3db501c4ac5a$f93f5d10$a501280a@.phx.gbl...
> Hi,
> Thanks for your reply. I didn't set any isolation
> level in my application, so i think by default it should
> be read uncommitted right? How to detect whether the data
> is in the buffer cache but the application run too fast?
> Thanks
>
> >--Original Message--
> >Could it be possible you are blocked due to your choice
> of isolation levels?
> >You could run sp_who and see if anyone is blocking you
> the next time your
> >app slows down. You could set to READ UNCOMMITTED if
> its acceptable by your
> >app. Then again, a commit by another app should not
> take that long, so you
> >might want to look at fixing the other app instead.
> >
> >Another possibility is that the data was in the buffer
> cache when the app
> >was fast.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup
> Lite, free!
> >
> >
> >"florencelee" <florencelee@.visualsolutions.com.my> wrote
> in message
> >news:158001c4ac52$2735ceb0$a401280a@.phx.gbl...
> >> Hi,
> >> Sometimes, in my application, when i browse a
> >> record, it takes very fast 1/2 seconds but sometimes
> it
> >> seems like hang there and take a very long time around
> >> 10 minutes to finish,could it be deadlock ? Any other
> >> suggestion?
> >>
> >> How to check and prevent from deadlock?
> >>
> >> thanks.
> >>
> >> regards,
> >>
> >> florence
> >>
> >
> >
> >.
> >
Friday, February 24, 2012
application error
My SSIS package will just hang (do nothing) after validation of the package tasks. I realised that it does 2 validation. It then hits "starting exectuing" and then nothing. I mean nothing. It just stays the same. When I look into the logg file, the same message as in the output window. My package has parallel extract of data from the same datasource, but different tables. I dont know if that the problem but i really doubt it because i have done parallel table downloads countless times in version 2000. When i go into the data task window, the source task does no even indicate that its downloading (color yellow). Is there any reason why this will happen? Ooo, but the tasks executes just fine when i execute them individually (right click > execute).
Perhaps there is some blocking on the source system? There could be in any number of reasons and I would hazard a guess that the problem isn't in SSIS.
-Jamie
|||what do you mean by the source system. I keep getting errors like the one below. Im pulling down a table called ACT with ACT source task and destination task. For some reason, after pulling down a couple of records, it generates this error or sometimes it just hangs.
[ACT SOURCE [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)|||
By source system I mean the "thing" from which you are extracting data.
What is the ACT source task? There is no such thing as a "source task" so I am assuming you mean "source component".
Is this a custom component (i.e. It is something that isn't provided out of the box with SSIS)?
-Jamie
|||
the act source task is an source component but not a custom source component. In any case, i changed the maxconcurrentexecution = -1 to 13. It seemed to start just fine but then failed at the very end when after pulling down almost all of the records. Now, this time the error was
[ACT SOURCE [1]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutp(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuf
and the warnings i got before that is
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Now, I dont know how this is possible but im running on a 64x xeon DP 3.02 with multi processor with nothing running on it except for SQL 2005 at present. It didnt look like it was even using 10% of the machines capabilities so i dont understand why it will complain about memory.
|||
Well, it looks from the copied text that you were running this package from the designer (BIDS). If that is the case then you are running in the WoW as a 32 bit process so you only would have a 2GB virtual address space.
Matt
|||Is there a work around to this problem so i have consume mor virtual addresss space the 2GB "if" that is the problem which i think might be because smaller tables are able to download without any problem.|||You can run the package using the 64 bit version of DTExec if you have installed the 64 bit version of SSIS. That is not limited by the 2GB virtual address space.
Matt
|||my odbc driver (DB/C 4) is a 32bit driver and that would mean if i should install SSIS in 64x, i wont be able to see my 32x odbc driver from SSIS because it will be registered in the data source manager for 32x under WOW or is there a way to go around this. Would you suggest that im better off with the 32x version of windows server 2003 even though i wont be using the full potential of the 64x machine (which i would like to ).
by the way, i installed the 32 version of SSIS
|||There is no way to do this if your driver is 32 bits. You would have to run the package in 32 bits and then have the limitation of the 2GB virtual address space (you could use the /3GB switch to get 3GB which can sometimes help, but I believe you would actually need to be running a 32 bit OS to use this switch, although I am not certain of this). Since you are running in the WoW you are running the package as if it was 32 bits so using a 32 bit OS wouldn't buy you anything (except as I mentioned perhaps the option of using the /3GB switch).
Matt
|||So basically, im screwed. Aaaaaaaaaaaaaaaa, i convienced my manager to get a 64x machine because it will make our process faster and because i was having the same memory problem on the development machine. He is going to cut my pay check into half unless Bill calls him and beg for me. My worry is if 2gb is an issue, it could be that 3gb will be an issue to since i cant really weigh how my virtual memory the processs will need. Im really in a pinch now.
why will the SQL team put a virtual memory limit on the 32x?
|||It is not the SQL team. The OS only provides 2GB of virtual address space to any individual process. A 32 bit number can only reference 4GB of address space and the OS reserves 2GB for system virtual address space and 2GB for application virtual address space (1 and 3 when using the /3GB switch, respectively). All applications are subject to this not just SSIS.
Matt
|||Matt David, could you please elaborate more on the 2gb. When im downloading a table, i suppose the data is held in memory till its all downloaded and then inserted. So if the entire data that im downloading is more the 2gb, you are saying its not possible. Or what you mean is that each field like int, string can hold a maximum of 2gb of data.|||
No the entire virtual memory address space is 2GB. That means everything, the application, the data, etc. SSIS tries to alleviate this by swapping to disk when in low memory condition but sometimes there is not enough swapable so it can still fail.
Also, we don't hold everything in memory. We hold buffers in memory and when they get to a destination they are inserted and then the memory is freed. However, depending on what you are doing in your dataflow we may not be able to swap/free enough to not fail due to out of memory conditions.
You should also note that virtual memory does need some physical memory backing so there are cases when you can not run out of virtual memory but will run out of the physical backing and this can also cause out of memory failures. You don't mention how much physical memory your PC has but generally this is not an issue on 64 bit boxes.
Matt
|||what were you doing at the time of the error?Idid not do anything however I finished work from internet explorer6.0 and I closed it befor I turn off computer when I closed it the error is appear alwayswhich language are you developing with?I did not devloping any language the langauge that I use in ienternet explorer is english and some time I open arabic site and comes to my mail messages in arabic I open it
does this happen in IE in general, even if you are not developing within the IDE? yes thats happen in IE in general
thanks for all help
Monday, February 13, 2012
Apparent System hang during intensive long running script: SQL Server 2000 Best Server Set
Hi,
Our db server appeared to hang during a long running script last
night. Our windows guys rebooted the server. However my suspicion is
that the script was consuming all systems resources but was still
running.
The SQL server is set to use all CPUs (4) and max all memory. It also
has raised priority on Windows. Does this sound like a possibility? If
so, what are the best settings for a db server, just so that we can
keep access during intensive queries?
When the server came back up, sql spent a great deal of time
recovering this database, presumably because it was mid-transaction.
Cheers,
James
MCDBAYou might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
SQL users. But also look at how the batch is written, as part of the problem might have been
blocking. For instance, perhaps you can split up some modifications over several transactions
instead of one big truncations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> Hi,
> Our db server appeared to hang during a long running script last
> night. Our windows guys rebooted the server. However my suspicion is
> that the script was consuming all systems resources but was still
> running.
> The SQL server is set to use all CPUs (4) and max all memory. It also
> has raised priority on Windows. Does this sound like a possibility? If
> so, what are the best settings for a db server, just so that we can
> keep access during intensive queries?
> When the server came back up, sql spent a great deal of time
> recovering this database, presumably because it was mid-transaction.
> Cheers,
> James
> MCDBA
>|||On Mar 29, 11:20 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> SQL users. But also look at how the batch is written, as part of the problem might have been
> blocking. For instance, perhaps you can split up some modifications over several transactions
> instead of one big truncations.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>
> > Hi,
> > Our db server appeared to hang during a long running script last
> > night. Our windows guys rebooted the server. However my suspicion is
> > that the script was consuming all systems resources but was still
> > running.
> > The SQL server is set to use all CPUs (4) and max all memory. It also
> > has raised priority on Windows. Does this sound like a possibility? If
> > so, what are the best settings for a db server, just so that we can
> > keep access during intensive queries?
> > When the server came back up, sql spent a great deal of time
> > recovering this database, presumably because it was mid-transaction.
> > Cheers,
> > James
> > MCDBA- Hide quoted text -
> - Show quoted text -
Thanks Tibor, A great help. On the windows priority, is that a well
known no-no? What problems does it cause?
Yes the script was a pile of erm ...
I wanted to add a new column but not as the last column so I scripted
out of EM. So million of records into a temp table, rename etc,
reindex etc etc. AND I put a transaction round the whole thing... Not
clever. Plus transaction backups were happening at the same time...
So I'm rewriting to simply add a NULLABLE column to the end of the
table using ALTER TABLE and removing the wrapping transaction. Should
be a lot faster...
And I thought I was a reasonable DBA... :o(
When doing my testing I was on the dev server using Simple Recovery
model so didn't hit the same problems.
Many thanks,
James|||On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> On Mar 29, 11:20 am, "Tibor Karaszi"
>
>
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> > SQL users. But also look at how the batch is written, as part of the problem might have been
> > blocking. For instance, perhaps you can split up some modifications over several transactions
> > instead of one big truncations.
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> > > Hi,
> > > Our db server appeared to hang during a long running script last
> > > night. Our windows guys rebooted the server. However my suspicion is
> > > that the script was consuming all systems resources but was still
> > > running.
> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> > > has raised priority on Windows. Does this sound like a possibility? If
> > > so, what are the best settings for a db server, just so that we can
> > > keep access during intensive queries?
> > > When the server came back up, sql spent a great deal of time
> > > recovering this database, presumably because it was mid-transaction.
> > > Cheers,
> > > James
> > > MCDBA- Hide quoted text -
> > - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James- Hide quoted text -
> - Show quoted text -
Is it bad practice to temporarily switch to SIMPLE recovery mode when
doing these kinds of operations? Everyone will be off the database at
the time... It's taking forever!|||> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
I believe it is still considered a no-no. I recall at some version that a combination of settings
would lift SQL Server to real-time priority. I don't think it can happen anymore, but in some
extreme cases, you might starve OS. Also, if you don't have anything else on the machine, then what
would SQL Server be higher than? I suggest you Google to see if you can find more official words on
this.
> I wanted to add a new column but not as the last column so I scripted
> out of EM.
Ugh. Yes, I can see why that happened. We all learn from our mistakes. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175169365.880312.134100@.y80g2000hsf.googlegroups.com...
> On Mar 29, 11:20 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
>> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> other
>> SQL users. But also look at how the batch is written, as part of the problem might have been
>> blocking. For instance, perhaps you can split up some modifications over several transactions
>> instead of one big truncations.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>>
>> > Hi,
>> > Our db server appeared to hang during a long running script last
>> > night. Our windows guys rebooted the server. However my suspicion is
>> > that the script was consuming all systems resources but was still
>> > running.
>> > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > has raised priority on Windows. Does this sound like a possibility? If
>> > so, what are the best settings for a db server, just so that we can
>> > keep access during intensive queries?
>> > When the server came back up, sql spent a great deal of time
>> > recovering this database, presumably because it was mid-transaction.
>> > Cheers,
>> > James
>> > MCDBA- Hide quoted text -
>> - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James
>|||> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations?
That depends on that backups you do for the database. Switching to simple will break the log backup
sequence, so talk to the backup administrator first!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
> On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
>> On Mar 29, 11:20 am, "Tibor Karaszi"
>>
>>
>> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
>> > higher
>> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> > other
>> > SQL users. But also look at how the batch is written, as part of the problem might have been
>> > blocking. For instance, perhaps you can split up some modifications over several transactions
>> > instead of one big truncations.
>> > --
>> > Tibor Karaszi, SQL Server
>> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
>> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>> > > Hi,
>> > > Our db server appeared to hang during a long running script last
>> > > night. Our windows guys rebooted the server. However my suspicion is
>> > > that the script was consuming all systems resources but was still
>> > > running.
>> > > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > > has raised priority on Windows. Does this sound like a possibility? If
>> > > so, what are the best settings for a db server, just so that we can
>> > > keep access during intensive queries?
>> > > When the server came back up, sql spent a great deal of time
>> > > recovering this database, presumably because it was mid-transaction.
>> > > Cheers,
>> > > James
>> > > MCDBA- Hide quoted text -
>> > - Show quoted text -
>> Thanks Tibor, A great help. On the windows priority, is that a well
>> known no-no? What problems does it cause?
>> Yes the script was a pile of erm ...
>> I wanted to add a new column but not as the last column so I scripted
>> out of EM. So million of records into a temp table, rename etc,
>> reindex etc etc. AND I put a transaction round the whole thing... Not
>> clever. Plus transaction backups were happening at the same time...
>> So I'm rewriting to simply add a NULLABLE column to the end of the
>> table using ALTER TABLE and removing the wrapping transaction. Should
>> be a lot faster...
>> And I thought I was a reasonable DBA... :o(
>> When doing my testing I was on the dev server using Simple Recovery
>> model so didn't hit the same problems.
>> Many thanks,
>> James- Hide quoted text -
>> - Show quoted text -
> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations? Everyone will be off the database at
> the time... It's taking forever!
>|||On Mar 29, 4:39 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations?
> That depends on that backups you do for the database. Switching to simple will break the log backup
> sequence, so talk to the backup administrator first!
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
>
> > On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> >> On Mar 29, 11:20 am, "Tibor Karaszi"
> >> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
> >> > higher
> >> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
> >> > other
> >> > SQL users. But also look at how the batch is written, as part of the problem might have been
> >> > blocking. For instance, perhaps you can split up some modifications over several transactions
> >> > instead of one big truncations.
> >> > --
> >> > Tibor Karaszi, SQL Server
> >> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> >> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> >> > > Hi,
> >> > > Our db server appeared to hang during a long running script last
> >> > > night. Our windows guys rebooted the server. However my suspicion is
> >> > > that the script was consuming all systems resources but was still
> >> > > running.
> >> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> >> > > has raised priority on Windows. Does this sound like a possibility? If
> >> > > so, what are the best settings for a db server, just so that we can
> >> > > keep access during intensive queries?
> >> > > When the server came back up, sql spent a great deal of time
> >> > > recovering this database, presumably because it was mid-transaction.
> >> > > Cheers,
> >> > > James
> >> > > MCDBA- Hide quoted text -
> >> > - Show quoted text -
> >> Thanks Tibor, A great help. On the windows priority, is that a well
> >> known no-no? What problems does it cause?
> >> Yes the script was a pile of erm ...
> >> I wanted to add a new column but not as the last column so I scripted
> >> out of EM. So million of records into a temp table, rename etc,
> >> reindex etc etc. AND I put a transaction round the whole thing... Not
> >> clever. Plus transaction backups were happening at the same time...
> >> So I'm rewriting to simply add a NULLABLE column to the end of the
> >> table using ALTER TABLE and removing the wrapping transaction. Should
> >> be a lot faster...
> >> And I thought I was a reasonable DBA... :o(
> >> When doing my testing I was on the dev server using Simple Recovery
> >> model so didn't hit the same problems.
> >> Many thanks,
> >> James- Hide quoted text -
> >> - Show quoted text -
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations? Everyone will be off the database at
> > the time... It's taking forever!- Hide quoted text -
> - Show quoted text -
So, in the end I did the following:
This solution relies on single user access to database, i.e kicking
everybody off. Also it relies on changing recovery model and is not
transaction safe. It is for large multi -million row updates. Smaller
updates don't need this stuff.
Stop log backups
Take FULL BACKUP
Script:
ALTER DATABASE audit_trail SET RECOVERY SIMPLE -- transaction log gets
truncated after transactions have completed. Stops log growing.
GO
-- Add the extra column as nullable
ALTER TABLE dbo.change_keys
ADD ordinal_position tinyint NULL -- NULLABLE means v little cost.
GO
-- Run big update in batches. test for appropriate batch size. At some
point performance will go bad.
-- i.e. in this case batches of 300000 took a ~1s, batches of 500000
took > 1min.
-- Update all change_keys records to set ordinal_position to 1. Do so
in optimised batches of 300,000 records.
SET NOCOUNT ON
DECLARE @.maxid int
DECLARE @.minid int
DECLARE @.currentid int
DECLARE @.step int
DECLARE @.count int
SET @.maxid = (SELECT MAX(change_id) FROM change_keys)
SET @.minid = (SELECT MIN(change_id) FROM change_keys)
SET @.currentid = @.minid
SET @.step = 300000
SET @.count = 0
WHILE @.currentid <= @.maxid
BEGIN
UPDATE change_keys WITH (TABLOCK) -- tablock useful as only user on
database - stops page locks having to escalate.
SET ordinal_position = 1
WHERE change_id BETWEEN @.currentid AND @.currentid + @.step - 1
SET @.count = @.count + @.@.rowcount
PRINT @.count
SET @.currentid = @.currentid + @.step
END
PRINT 'Total updated records: ' + CONVERT(varchar(15), @.count)
GO
-- Now make ordinal_position NOT NULL, if necessary
ALTER TABLE dbo.change_keys
ALTER COLUMN ordinal_position tinyint NOT NULL
GO
-- Put db back into FULL recovery mode.
ALTER DATABASE audit_trail SET RECOVERY FULL
GO
I have simplified from what I needed to do, but hopefully this is
helpful to someone...
Thanks again, Tibor.
Cheers,
James
Our db server appeared to hang during a long running script last
night. Our windows guys rebooted the server. However my suspicion is
that the script was consuming all systems resources but was still
running.
The SQL server is set to use all CPUs (4) and max all memory. It also
has raised priority on Windows. Does this sound like a possibility? If
so, what are the best settings for a db server, just so that we can
keep access during intensive queries?
When the server came back up, sql spent a great deal of time
recovering this database, presumably because it was mid-transaction.
Cheers,
James
MCDBAYou might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
SQL users. But also look at how the batch is written, as part of the problem might have been
blocking. For instance, perhaps you can split up some modifications over several transactions
instead of one big truncations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> Hi,
> Our db server appeared to hang during a long running script last
> night. Our windows guys rebooted the server. However my suspicion is
> that the script was consuming all systems resources but was still
> running.
> The SQL server is set to use all CPUs (4) and max all memory. It also
> has raised priority on Windows. Does this sound like a possibility? If
> so, what are the best settings for a db server, just so that we can
> keep access during intensive queries?
> When the server came back up, sql spent a great deal of time
> recovering this database, presumably because it was mid-transaction.
> Cheers,
> James
> MCDBA
>|||On Mar 29, 11:20 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> SQL users. But also look at how the batch is written, as part of the problem might have been
> blocking. For instance, perhaps you can split up some modifications over several transactions
> instead of one big truncations.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>
> > Hi,
> > Our db server appeared to hang during a long running script last
> > night. Our windows guys rebooted the server. However my suspicion is
> > that the script was consuming all systems resources but was still
> > running.
> > The SQL server is set to use all CPUs (4) and max all memory. It also
> > has raised priority on Windows. Does this sound like a possibility? If
> > so, what are the best settings for a db server, just so that we can
> > keep access during intensive queries?
> > When the server came back up, sql spent a great deal of time
> > recovering this database, presumably because it was mid-transaction.
> > Cheers,
> > James
> > MCDBA- Hide quoted text -
> - Show quoted text -
Thanks Tibor, A great help. On the windows priority, is that a well
known no-no? What problems does it cause?
Yes the script was a pile of erm ...
I wanted to add a new column but not as the last column so I scripted
out of EM. So million of records into a temp table, rename etc,
reindex etc etc. AND I put a transaction round the whole thing... Not
clever. Plus transaction backups were happening at the same time...
So I'm rewriting to simply add a NULLABLE column to the end of the
table using ALTER TABLE and removing the wrapping transaction. Should
be a lot faster...
And I thought I was a reasonable DBA... :o(
When doing my testing I was on the dev server using Simple Recovery
model so didn't hit the same problems.
Many thanks,
James|||On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> On Mar 29, 11:20 am, "Tibor Karaszi"
>
>
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> > SQL users. But also look at how the batch is written, as part of the problem might have been
> > blocking. For instance, perhaps you can split up some modifications over several transactions
> > instead of one big truncations.
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> > > Hi,
> > > Our db server appeared to hang during a long running script last
> > > night. Our windows guys rebooted the server. However my suspicion is
> > > that the script was consuming all systems resources but was still
> > > running.
> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> > > has raised priority on Windows. Does this sound like a possibility? If
> > > so, what are the best settings for a db server, just so that we can
> > > keep access during intensive queries?
> > > When the server came back up, sql spent a great deal of time
> > > recovering this database, presumably because it was mid-transaction.
> > > Cheers,
> > > James
> > > MCDBA- Hide quoted text -
> > - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James- Hide quoted text -
> - Show quoted text -
Is it bad practice to temporarily switch to SIMPLE recovery mode when
doing these kinds of operations? Everyone will be off the database at
the time... It's taking forever!|||> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
I believe it is still considered a no-no. I recall at some version that a combination of settings
would lift SQL Server to real-time priority. I don't think it can happen anymore, but in some
extreme cases, you might starve OS. Also, if you don't have anything else on the machine, then what
would SQL Server be higher than? I suggest you Google to see if you can find more official words on
this.
> I wanted to add a new column but not as the last column so I scripted
> out of EM.
Ugh. Yes, I can see why that happened. We all learn from our mistakes. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175169365.880312.134100@.y80g2000hsf.googlegroups.com...
> On Mar 29, 11:20 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
>> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> other
>> SQL users. But also look at how the batch is written, as part of the problem might have been
>> blocking. For instance, perhaps you can split up some modifications over several transactions
>> instead of one big truncations.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>>
>> > Hi,
>> > Our db server appeared to hang during a long running script last
>> > night. Our windows guys rebooted the server. However my suspicion is
>> > that the script was consuming all systems resources but was still
>> > running.
>> > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > has raised priority on Windows. Does this sound like a possibility? If
>> > so, what are the best settings for a db server, just so that we can
>> > keep access during intensive queries?
>> > When the server came back up, sql spent a great deal of time
>> > recovering this database, presumably because it was mid-transaction.
>> > Cheers,
>> > James
>> > MCDBA- Hide quoted text -
>> - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James
>|||> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations?
That depends on that backups you do for the database. Switching to simple will break the log backup
sequence, so talk to the backup administrator first!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
> On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
>> On Mar 29, 11:20 am, "Tibor Karaszi"
>>
>>
>> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
>> > higher
>> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> > other
>> > SQL users. But also look at how the batch is written, as part of the problem might have been
>> > blocking. For instance, perhaps you can split up some modifications over several transactions
>> > instead of one big truncations.
>> > --
>> > Tibor Karaszi, SQL Server
>> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
>> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>> > > Hi,
>> > > Our db server appeared to hang during a long running script last
>> > > night. Our windows guys rebooted the server. However my suspicion is
>> > > that the script was consuming all systems resources but was still
>> > > running.
>> > > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > > has raised priority on Windows. Does this sound like a possibility? If
>> > > so, what are the best settings for a db server, just so that we can
>> > > keep access during intensive queries?
>> > > When the server came back up, sql spent a great deal of time
>> > > recovering this database, presumably because it was mid-transaction.
>> > > Cheers,
>> > > James
>> > > MCDBA- Hide quoted text -
>> > - Show quoted text -
>> Thanks Tibor, A great help. On the windows priority, is that a well
>> known no-no? What problems does it cause?
>> Yes the script was a pile of erm ...
>> I wanted to add a new column but not as the last column so I scripted
>> out of EM. So million of records into a temp table, rename etc,
>> reindex etc etc. AND I put a transaction round the whole thing... Not
>> clever. Plus transaction backups were happening at the same time...
>> So I'm rewriting to simply add a NULLABLE column to the end of the
>> table using ALTER TABLE and removing the wrapping transaction. Should
>> be a lot faster...
>> And I thought I was a reasonable DBA... :o(
>> When doing my testing I was on the dev server using Simple Recovery
>> model so didn't hit the same problems.
>> Many thanks,
>> James- Hide quoted text -
>> - Show quoted text -
> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations? Everyone will be off the database at
> the time... It's taking forever!
>|||On Mar 29, 4:39 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations?
> That depends on that backups you do for the database. Switching to simple will break the log backup
> sequence, so talk to the backup administrator first!
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
>
> > On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> >> On Mar 29, 11:20 am, "Tibor Karaszi"
> >> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
> >> > higher
> >> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
> >> > other
> >> > SQL users. But also look at how the batch is written, as part of the problem might have been
> >> > blocking. For instance, perhaps you can split up some modifications over several transactions
> >> > instead of one big truncations.
> >> > --
> >> > Tibor Karaszi, SQL Server
> >> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> >> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> >> > > Hi,
> >> > > Our db server appeared to hang during a long running script last
> >> > > night. Our windows guys rebooted the server. However my suspicion is
> >> > > that the script was consuming all systems resources but was still
> >> > > running.
> >> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> >> > > has raised priority on Windows. Does this sound like a possibility? If
> >> > > so, what are the best settings for a db server, just so that we can
> >> > > keep access during intensive queries?
> >> > > When the server came back up, sql spent a great deal of time
> >> > > recovering this database, presumably because it was mid-transaction.
> >> > > Cheers,
> >> > > James
> >> > > MCDBA- Hide quoted text -
> >> > - Show quoted text -
> >> Thanks Tibor, A great help. On the windows priority, is that a well
> >> known no-no? What problems does it cause?
> >> Yes the script was a pile of erm ...
> >> I wanted to add a new column but not as the last column so I scripted
> >> out of EM. So million of records into a temp table, rename etc,
> >> reindex etc etc. AND I put a transaction round the whole thing... Not
> >> clever. Plus transaction backups were happening at the same time...
> >> So I'm rewriting to simply add a NULLABLE column to the end of the
> >> table using ALTER TABLE and removing the wrapping transaction. Should
> >> be a lot faster...
> >> And I thought I was a reasonable DBA... :o(
> >> When doing my testing I was on the dev server using Simple Recovery
> >> model so didn't hit the same problems.
> >> Many thanks,
> >> James- Hide quoted text -
> >> - Show quoted text -
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations? Everyone will be off the database at
> > the time... It's taking forever!- Hide quoted text -
> - Show quoted text -
So, in the end I did the following:
This solution relies on single user access to database, i.e kicking
everybody off. Also it relies on changing recovery model and is not
transaction safe. It is for large multi -million row updates. Smaller
updates don't need this stuff.
Stop log backups
Take FULL BACKUP
Script:
ALTER DATABASE audit_trail SET RECOVERY SIMPLE -- transaction log gets
truncated after transactions have completed. Stops log growing.
GO
-- Add the extra column as nullable
ALTER TABLE dbo.change_keys
ADD ordinal_position tinyint NULL -- NULLABLE means v little cost.
GO
-- Run big update in batches. test for appropriate batch size. At some
point performance will go bad.
-- i.e. in this case batches of 300000 took a ~1s, batches of 500000
took > 1min.
-- Update all change_keys records to set ordinal_position to 1. Do so
in optimised batches of 300,000 records.
SET NOCOUNT ON
DECLARE @.maxid int
DECLARE @.minid int
DECLARE @.currentid int
DECLARE @.step int
DECLARE @.count int
SET @.maxid = (SELECT MAX(change_id) FROM change_keys)
SET @.minid = (SELECT MIN(change_id) FROM change_keys)
SET @.currentid = @.minid
SET @.step = 300000
SET @.count = 0
WHILE @.currentid <= @.maxid
BEGIN
UPDATE change_keys WITH (TABLOCK) -- tablock useful as only user on
database - stops page locks having to escalate.
SET ordinal_position = 1
WHERE change_id BETWEEN @.currentid AND @.currentid + @.step - 1
SET @.count = @.count + @.@.rowcount
PRINT @.count
SET @.currentid = @.currentid + @.step
END
PRINT 'Total updated records: ' + CONVERT(varchar(15), @.count)
GO
-- Now make ordinal_position NOT NULL, if necessary
ALTER TABLE dbo.change_keys
ALTER COLUMN ordinal_position tinyint NOT NULL
GO
-- Put db back into FULL recovery mode.
ALTER DATABASE audit_trail SET RECOVERY FULL
GO
I have simplified from what I needed to do, but hopefully this is
helpful to someone...
Thanks again, Tibor.
Cheers,
James
Subscribe to:
Posts (Atom)