We help IT Professionals succeed at work.

SQL server performance baseline

marrowyung
marrowyung asked
on
141 Views
Last Modified: 2018-03-28
hi,

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

I want to bench mark that.
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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....
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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:

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions