killer query - limit usage ?

Windows 2003 has a feature called WSRM that can limit resources by app. I
guess you could limit the amount of CPU taken up by express but that would
not be the long term solution. You need to tune the database and the app
that is hitting the database so that it doesn't use too much resources in
the first place. Too much data is not an answer it is how it is being used.
But the more data and the heavier the usage the more likely that you need to
upgrade to another edition of SQL Server. Here are some links that may get
you started.
http://www.sql-server-performance.com/sql_server_performance_audit10.asp
Performance Audit
http://www.microsoft.com/technet/prodtechnol/sql/2005/library/operations.mspx
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
http://sqldev.net/misc/WaitTypes.htm Wait Types
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:u%23jrUgytHHA.2004@.TK2MSFTNGP03.phx.gbl...
> must be a way to limit cpu server side too !
> too much data is the prob.
>
That query will hardly use enough CPU to measure, so limiting CPU
would not get you very far. The query is limited by disk speed for
reads and/or network speed for returning the results. I don't see any
way to throttle either one, but then I don't expect such a simple
query to cause much of a bottleneck.
Roy Harvey
Beacon Falls, CT
On Tue, 26 Jun 2007 09:04:07 +0100, "Scott" <s@.yahoo.co.uk> wrote:

>quick question. How can query optimization help my query:
>select * from [table]
>when my table has 5 million records.
>surly the only way to sort this is to limit the cpu ?
>sorry to keep posting
>scott
>
|||On Jun 26, 2:04 pm, "Scott" <s...@.yahoo.co.uk> wrote:
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
Hi, select * from [table] with 5 million records will not slow your
server down. However if you issue an update then it may slow things
down and you may need to optimize your query. You can also restrict
access to large tables if that solves the problem.
|||Keeping in mind the things already said about your query why would you do
something like that in the first place? What are you going to do with all
the columns and all the 5 million rows? A human certainly isn't going to
make sense of that much data. If you only need a few of them you need to add
a proper WHERE clause and indexes to support it.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:%23vpOve8tHHA.4612@.TK2MSFTNGP04.phx.gbl...
> quick question. How can query optimization help my query:
> select * from [table]
> when my table has 5 million records.
> surly the only way to sort this is to limit the cpu ?
> sorry to keep posting
> scott
>
|||Not sure what you mean by option2 but if you place the filegroup or database
in read only mode sql server will not take out any locks when you read it
since it knows no one can change it while you read. If no one is making
changes you can also use the READ UNCOMMITED isolation level to achieve the
same end results.
Andrew J. Kelly SQL MVP
"Scott" <s@.yahoo.co.uk> wrote in message
news:O5L%23RnkuHHA.3544@.TK2MSFTNGP03.phx.gbl...
> very good point Andrew, sorry for being a little dim.
> i read something about a READ ONLY option 2 which is supposed to speed up
> queries.
> Thanks for your time, great help
> Scott
>

Killed/Rollback process hogging ALL CPU resources.

I have a test database for the end users to test their select queries for reports.
One of my users is writing queries that cause locking in the database. I killed the process last evening and they are in Killed/Rollback status but are still hogging 90% of the CPU resources for the past 12 hrs. I tried killing them several times but no go.

I know that the best way to clear of these processes is by restarting SQL Server. If that is not an option is there is any other way we can clean these processes?

Also the user running these queries has a read only and create view access to the database. From my experience processes that go into Kill/Rollback state after you kill them are processes associated with some update transaction. Since the user as far as i know is running Select commands would an infinite loop cause this ?

thanks
ninaWhat a good time to talk about execute only authorit to stored procedures...

Your rool back can take up 2 twice as long as the original process...maybe longer...

I doubt it was select only...any chance a work table was involved with millions of rows and they did a delete to clear it out?

Guess you don't have the opportunity to do a code review...

If you stop and restart the server, it'll just pick up from where it left off.

What version is this?

Is this a dev or production box?

I know I saw someone once who discussed this...but it was messy

Before you issue a kill, you should find out what the spid was doing...did you do sp_who to see how much I/O and CPU it was using?

Do you monitor the developers with profiler?

What login Id did the developer login with?|||Hello Brett
thanks for responding. This is a development box and that is probably the only good thing about this entire mess.
And no i killed the process without actually looking into the query that it was running. It is SQL Server 2000 box and the user has a SQL Server account and he uses query analyzer to write/test his queries.
The user has create view rights and belongs to db_datareader role for just the one test database on the server.
Would a query running into an infinite loop cause this problem ?
Before killing the process it was using about 70% of CPU but it kept hogging more resources through the night after i killed it and this morning everything on the server came to a standstill as it hogged 99% of CPU|||Put the user in the pillory, until the rollback is complete. They should learn after that ;-)|||The rollback ran through the night and ate up all our server resources and still did not complete. I just went in and restarted the server. Since this is a development environment it was not that much of a problem.

What i would like to know is that was restarting the SQLServer the only option that we have in such a situation ? And also would a select query every cause a rollback ?|||My guess is that if the restart worked then it wasn't rolling back...

Did you check and see if to spids where deadlocked?|||Yes i did check for deadlocks and there were none in the system.|||OK, Try this next time

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

That will throw everyone out without having to issue a kill

btw did you kill all spids?|||Thanks will keep the Alter statement for future reference. And yes i did try to kill all the processes accociated with that user. And since there were only 4-5 processes for that user i know i got them all.