February 6, 2008 at 4:06 am
Hi all
We've got a user that needs the ability to write and execute SQL code against our server.
However, she often writes hefty inefficient queries that cane the machine.
I know in SQL 2008 Katmai there is a Query Resource Governor that allows you to limit CPU usage by user or role, but how could I implement an equivalent on 2005?
How about a TSQL wrapper I could put around her code that could restict the number of available CPUS for her SPID to 1?
The machine is an HP DL 585, 4 x Dual Core AMD Opteron x64, 16GB RAM.
Running Windows Server 2003 x64 R2 , SQL Server 2005 x64 SP2
Cheers
Rich
February 6, 2008 at 4:30 am
You could insist that she adds OPTION (MAXDOP 1) to the end of each of her queries. Not ideal, but it might work.
You can use the governor that's in SQL 2005 to prevent expensive queries from running at all.
As a longer term 'fix' maybe suggest to this person that she gets some training in writing more optimal SQL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 5:09 am
Gail
To clarify your solution would be to run something like this in the same query window?
(I would have thought the setting would need to be above her TSQL code?
execute uspHogTheServer
OPTION (MAXDOP 1)
So would the SPID she is on will only hog CPU0?
I have 4 processors with dual cores which show up in SQL as CPu0 to CPU7.
Would it grab one of the processors with i.e CPU0 and CPU1?
Or would it just grab one of the processor cores CPU0?
Cheers
Rich
February 6, 2008 at 5:15 am
No. It's a query hint. It must be applied to a query, not a storedProc. It only applies to the query it's part of.
SELECT Lots, of, columns
FROM ReallyBigTable
WHERE BadlyWrittenWhereClause = 1
OPTION (MAXDOP 1)
A query written as such will use one processor, whichever one the connection ends up running on. No way to say which one
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 5:37 am
The stored procedures she is writing mostly consist of
create proc uspHogTheServer as
select * INTO #tmpHumongous from Massive
INSERT INTO #tmpHumongous
select * from Gigantic
SELECT * FROM #tmpHumongous
go
So I would get her to write something like :
Alter proc uspHogTheServer as
select * INTO #tmpHumongous from Massive option (maxdop 1)
INSERT INTO #tmpHumongous
select * from Gigantic option (maxdop 1)
SELECT * FROM #tmpHumongous option (maxdop 1)
go
????????
February 6, 2008 at 5:43 am
Yup. Petty much.
I'd definitly suggest you see if she can get some SQL training, formally or informally. How long's it going to be before she does something like write an update/delete and forget the where clause? (Or does she just have select rights?)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 6:14 am
To be fair to her she is working with a lot of data and she should have her own datawarehouse environment.
This is not possible at the moment.
Even if the queries and procs were well writen SQL server would still allow her to consume a lot of server resources.
Many thanks for your kind help
Richard
February 6, 2008 at 8:00 am
What about creating optimized views for her to query against?
Just a thought.
Greg
February 6, 2008 at 8:05 am
that's a good idea.
also, trace her queries and see if you can add some indexes to help her out.
my guess is that this person is querying the same stuff most often.
long term is to get a reporting db for her and other end users to query against AND to teach her and others how to write efficient queries (get rid of those massive temp tables).
Greg J
Gregory A Jackson MBA, CSM
February 6, 2008 at 8:35 am
You can't give her a local copy of the DB? Disk is very, very cheap now. US$1000 for 1TB.
If not, there's no good resource governer in 2005 other than Gail's suggestion if the queries need to run.
February 6, 2008 at 8:45 am
you could install a second instance of sql2005 and use Windows system resource manager (WSRM)
to govern CPU usage %.
WSRM is also available for x64 !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 6, 2008 at 12:08 pm
you'd be better off just launching a new thread.
but since we're at it.
afaik you can only sample and guestimate.
Keep in mind connections come and go...
fairly common usage of sysprocesses ....
e.g.
if (object_id('tempdb..#tmpVerbruik') is null)
begin
-- tmptabelleke aanmaken
select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'theoneandonly'
group by A.hostname, A.loginame, A.program_name
end
select A1.*
--, T.min_login_time
--, T.max_last_batch
--, T.sum_cpu
--, T.sum_physical_io
--, T.sum_memusage
, A1.sum_cpu - T.sum_cpu as Delta_cpu
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
, A1.sum_physical_io - T.sum_physical_io as Delta_physical_io
, A1.sum_memusage - T.sum_memusage as Delta_memusage
from
(select min( @@Servername ) as ServerName
, A.hostname
, A.loginame
, A.program_name
, min(login_time) as min_login_time, max(last_batch) as max_last_batch , sum(cpu) as sum_cpu, sum(physical_io) as sum_physical_io, sum(memusage) as sum_memusage
--, login_time, last_batch , cpu, physical_io, memusage
-- into #tmpVerbruik
FROM master..sysprocesses A
--WHERE loginame = 'theoneandonly'
group by A.hostname, A.loginame, A.program_name
) A1
left join #tmpVerbruik T
on A1.hostname = T.hostname
and A1.loginame = T.loginame
and A1.program_name = T.program_name
where T.sum_cpu <> A1.sum_cpu
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
or T.sum_physical_io <> A1.sum_physical_io
or T.sum_memusage <> A1.sum_memusage
order by Delta_CPU desc, A1.hostname, A1.loginame, A1.program_name
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 25, 2008 at 10:25 am
Hi,
Just trawling the site looking for another CPU usage issue and came across this thread.
I have a boss who write very clever but very large queries and then pesters the life out of me to explain why they take so long, 40 minutes plus.
Not long ago the light dawned on him; build the records up in small inserts and suddenly his query took 3 minutes. Get the programmer to insert in relatively small batches which can be done quite easily if the table has an identity column.
Also the line select * INTO #tmpHumongous from Massive fills me with dread and could actually lock the tempdb. Much better to do
select * INTO #tmpHumongous from Massive where 1=0
NSERT INTO #tmpHumongous
Select * from Massive preferably in a loop as shown below.
If you have an identity column with an index then its even better to.
Create table #ids(id_column int)
Declare @ id_column int
Declare @ id_column_offset int
Select @ id_column = starting id
Select @ id_column_offset = no of rows to walk
/* by adjusting @ id_column_offset you should be able to tune both queries to use an index seek and minimal resources */
While @ id_column < @max_id_value
Begin
Truncate table #ids
Insert into #ids (id_column) select id_column from Massive where Massive between @ id_column and @ id_column + @ id_column_offset
Insert into #tmpHumongous
Select * from Massive Mas
Join #ids id on Mas. id_column = id. id_column
Select @ id_column = @ id_column + @ id_column_offset
End
June 26, 2008 at 5:47 am
Yuckon (2/6/2008)
The stored procedures she is writing mostly consist ofcreate proc uspHogTheServer as
select * INTO #tmpHumongous from Massive
INSERT INTO #tmpHumongous
select * from Gigantic
SELECT * FROM #tmpHumongous
go
Why don't you consider
SELECT * FROM Massive
UNION ALL
SELECT * FROM Gigantic
No need for any temporary tables in that case. Or am I missing something here?
June 26, 2008 at 8:55 pm
Ronald Cartmale (6/25/2008)
select * INTO #tmpHumongous from Massive where 1=0
You don't need to do that... that problem was solved way back in 6.5.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply