SQL server performance baseline

marrowyung
marrowyung used Ask the Experts™
on
hi,

usually what tools you guys build a performance baseline before rebuild index done ?

I want to bench mark that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
No tools. Just take the average time for the query before the rebuild and then after so you can compare both.
marrowyungSenior Technical architecture (Data)

Author

Commented:
yes, this is the only thing I am doing but I want to do in a much robust way so that I can scan/check it again AFTER I rebuilt index.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Matt is looking at my questino....
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know what else you can add for your benchmark.
Fragmentation level and Query execution plan and times should be the information related to index that you may want to keep.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Fragmentation level" yes but management don't look at that. I will use ola solution for that, but some times I found ola has miss index to rebuild.

" Query execution plan "

we might just see EE is smaller and not that complex, but again, management don't read that.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
If it's  a report for Managers then just write about the database reduction size and query performance after rebuild.
And Ola's script has everything you need to rebuild indexes. Is more than tested over all these years by hundreds or even thousands of DBAs.
marrowyungSenior Technical architecture (Data)

Author

Commented:
" query performance after rebuild."

noted that already but should I ask dev team to bench mark their application?

 "And Ola's script has everything you need to rebuild indexes. Is more than tested over all these years by hundreds or even thousands of DBAs."

I knew and that's why I use it.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Developers can (or should) only obtain data from Development Environment that usually isn't the same for Production Environment.
IMO, Application Managers should be the ones to provide those benchmarks. Developers are there to fix and improve based in the benchmark reports.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Developers can (or should) only obtain data from Development Environment that usually isn't the same for Production Environment.


I understand, always not the same, so i ask them anyway.

"Application Managers should be the ones to provide those benchmarks"

Excellent.

ok one last thing, They can say they don't know/have and I ask them to prepare one and they don't know how. :):),  I am not sure if DB monitoring tools will give this as I never make use of that before but per queries bench marking.
IT Engineer
Distinguished Expert 2017
Commented:
They can say they don't know/have and I ask them to prepare one and they don't know how
We usually expect that people performing they tasks know what they are doing. If not, then maybe they aren't the right person for that job.
marrowyungSenior Technical architecture (Data)

Author

Commented:
that's why I don't know why recently developer blame DBA on why their web system is slow !

the coder can hide!

some US culture can do sth like they build the code, they don't care it is fast or slow, then push to DB team for tuning.

This is the way of querying the DB make it slow. if they don't learn how to code right in SQL server, I always can't see why they are hired.

in your side, who are expected to tune the SQL code ? DBA or developer ? one DBA can't tune all code by all developer.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
in your side, who are expected to tune the SQL code ? DBA or developer ? one DBA can't tune all code by all developer.
In a first instance, developers but DBA can help them tune if they find something that is taking too long to complete. But this is the exception and not the rule. DBAs should work more on the server level (Resources, Security, Monitoring, Backups, ...) and Developers on the database level (permissions, queries, ...).
marrowyungSenior Technical architecture (Data)

Author

Commented:
"Developers on the database level (permissions, queries, ...)."

for permission level , I will move it to DBA, specially infrastructure DBA like me, this can keep me busy.... :):)

queries is more on developer from my point of view as it involve business logic.

I tried to tune queries for developer and verified number of rows is the same, finally developer STILL says that my logic is wrong !!
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
for permission level , I will move it to DBA, specially infrastructure DBA like me, this can keep me busy.
I'm not talking about permissions on SQL Server instance (Logins) but on database level (users) as who can perform SELECT, INSERT, DELETE or UPDATE, who can execute stored procedures or functions. Creation of database role and schemas. This is more at logical level and a DBA shouldn't interfere.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"I'm not talking about permissions on SQL Server instance (Logins) but on database level (users) as who can perform SELECT, INSERT, DELETE or UPDATE, who can execute stored procedures or functions. "

I'd like to classify it still DBA's job to make them has more job to do.

"This is more at logical level "

business logic ?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
business logic ?
Correct. DBAs doesn't have nothing to do with application requirements in terms of who should have access to what (tables, views, stored procedures, functions, ...). That's a development thing.
marrowyungSenior Technical architecture (Data)

Author

Commented:
ok. here, defination is a bit diff.
marrowyungSenior Technical architecture (Data)

Author

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial