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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment