Showing posts with label increase. Show all posts
Showing posts with label increase. Show all posts

Friday, March 30, 2012

performance tuning

how to increase performance through management studio
except making indexeshow to increase performance through management studio
except making indexes

Wow. Good luck with that. Let us know how it goes.

hmscott|||Hows about writing more efficient SQL - that's always a good way...

ankur: a vague question will only ever get a vague answer.|||Good database design. Well written queries. Well thought out indexes. A decent layout of data and log files on disk. Just a few of the things you want to consider, there's no single magic pill answer. Tuning a database server is like tuning a car - make the engine more powerful and you need a better exhaust, and a bigger gas tank as it's less fuel efficient, which add weight.
More power means better brakes and better cooling, which means more weight. Which in turn means more power and better brakes. Change one thing and it affects others, you can't alter anything in isolation in a database server without knowing the knock on effect. Point I'm making is you need to learn about the whole thing, not just one in isolation. There is no magic, single answer - unless you just turn it over to a decent DBA who's skilled in knowing what he's doing. This is where Management Studio stops poviding the answers and your brain has to start working hard, it's an aid to your mind, not a replacement ! Dig into SQL Server Books Online and start learning is my best advice :)|||I just have to say: what a fantastic answer! :beer:

Monday, February 20, 2012

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon job
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,
Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:

> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon jo
b
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads
,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:

> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon
job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,

Performance Monitor for Indexes

I have a SQL Server 2000 database with numerous indexes. The database
developers have reworked the indexes to increase database performance.
This database has Windows 2003 as the operating system on this server.
What are the performance monitor counters that I should add to my perfmon job
to collect statistic before and after the indexes have been modified?
Please help me with this task.
Thanks,Indexing will have most impact on disk io, so I would monitor PhysicalDisk:
%Read time and PhysicalDisk %Write time for each disk. You should also look
at CPU changes by monitoring the cpu counters, and also Average Disk Queue
length.
My preferred approach in this case though is to use SQL Profiler and run a
trace before adding indexes and trace to a table. Then add your indexes and
run the same workload through.
Then query your trace tables to find how your longest runnnig queries before
indexing have improved. You will be able to examine duration of query, reads,
writes and cpu. This will give you more information and suggest to you
queries that are still not performing well after you apply your indexing
script.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Joe K." wrote:
> I have a SQL Server 2000 database with numerous indexes. The database
> developers have reworked the indexes to increase database performance.
> This database has Windows 2003 as the operating system on this server.
> What are the performance monitor counters that I should add to my perfmon job
> to collect statistic before and after the indexes have been modified?
> Please help me with this task.
> Thanks,