February 18, 2010 at 12:52 pm
Open up performance monitor and select the appropriate disk and cpu counters.
Here is a document from Microsoft on it
http://msdn.microsoft.com/en-us/library/cc966540.aspx
And another article by Grant Fritchey here:
http://www.simple-talk.com/sql/performance/sql-server-performance-crib-sheet/#seventh
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 18, 2010 at 1:47 pm
Rookie question, there is literally tons of literature regarding Perfmon, and tons of queries.
Do you have an example of a query you want me to run?
I also only have developers edition, so i cant run the performance data collector!
Thanks
February 18, 2010 at 1:59 pm
In the activity monitor on the Server i have Waiting Tasks(7)....whatever that means ?
I have Waiting Tasks(0) on the desktop.
February 18, 2010 at 2:28 pm
You should be able to use Data Collector. Developer Edition has all the functionality of Enterprise Edition, just that it can't be used in a production environment.
February 18, 2010 at 5:12 pm
isuckatsql (2/18/2010)
In the activity monitor on the Server i have Waiting Tasks(7)....whatever that means ?I have Waiting Tasks(0) on the desktop.
Waiting Tasks
The number of tasks that are waiting for processor, I/O, or memory resources.
This means there are other things on your server trying to get CPU time and waiting for it.
Do you have other apps on this server? Are there other SQL processes, scripts, jobs running on this server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2010 at 7:19 am
Nothing else running on the server!
I did notice a hotfix for a bug, which seems to incorrectly provide a high value to one of the wait tasks.
But that just seems like a counter error.
February 19, 2010 at 7:41 am
Did you try running a query on master.dbo.sysprocesses tp find out what resource wait it has and what was the previous wait? It could point you to the right direction.
-Roy
February 19, 2010 at 10:38 am
I ran the sysprocesses query, but dont know what i should be looking for.
Server has FSAGENT apprearing a lot, but this was the wait that had the hotfix issue from Microsoft.
Desktop dows not have a FSAGENT value.
February 19, 2010 at 11:32 am
I got perfmon running and did a test on my 60 GB DB accessing 2.25 Million records.
During a 2 minute test, it accessed approx 155 000 records, results below.
Cpu - varied up to 19% - 100% Max frequency
Disk - varied up to 49 MB/sec - up to 100% Highest Active Time
Network - varied up to 74 kpbs - 0% Network Utilization
Memory - varied up to 144 Hard Faults/sec - 98% Used Physical memory
February 19, 2010 at 11:40 am
Removed
Tim Januario
February 19, 2010 at 12:14 pm
isuckatsql (2/19/2010)
I got perfmon running and did a test on my 60 GB DB accessing 2.25 Million records.During a 2 minute test, it accessed approx 155 000 records, results below.
Cpu - varied up to 19% - 100% Max frequency
Disk - varied up to 49 MB/sec - up to 100% Highest Active Time
Network - varied up to 74 kpbs - 0% Network Utilization
Memory - varied up to 144 Hard Faults/sec - 98% Used Physical memory
Your disk and CPU are a bit disconcerting to me.
I would have to go back to the disk alignment, or that there is serious fragmentation.
Based on your scripts, I would go back and create a clustered index on the zips table. Add a new field to use as your primary key and clustered index.
Then rerun your queries and let us know.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 19, 2010 at 12:33 pm
CirquedeSQLeil (2/19/2010)
isuckatsql (2/19/2010)
I got perfmon running and did a test on my 60 GB DB accessing 2.25 Million records.During a 2 minute test, it accessed approx 155 000 records, results below.
Cpu - varied up to 19% - 100% Max frequency
Disk - varied up to 49 MB/sec - up to 100% Highest Active Time
Network - varied up to 74 kpbs - 0% Network Utilization
Memory - varied up to 144 Hard Faults/sec - 98% Used Physical memory
Your disk and CPU are a bit disconcerting to me.
I would have to go back to the disk alignment, or that there is serious fragmentation.
Based on your scripts, I would go back and create a clustered index on the zips table. Add a new field to use as your primary key and clustered index.
Then rerun your queries and let us know.
I think you have hit the nail on the head.......Defragment!
I have not done a defrag for quite a while, to be honest i just had a senior moment and forgot...duh!
I will defrag all drives this weekend.
Thanks!
February 19, 2010 at 1:05 pm
Let us know how that goes.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 20, 2010 at 7:14 am
Defragged all drives and rebooted the server, no improvement in query performance 🙁
February 20, 2010 at 7:54 am
Too lazy to look back, did you post the table and index definitions and the actual execution plans from running the queries on the two systems? You can save the graphical plans as .sqlplan files, zip them up together and post them. This will help us figure out what maybe happening.
Viewing 15 posts - 46 through 60 (of 66 total)
You must be logged in to reply to this topic. Login to reply