Not a DBA admin - How do i optimize/tune my queries, check on missing index?

Question

Not a DBA admin - How do i optimize/tune my queries, check on missing index?

quentin.harris

SSC Eights!

Points: 924
More actions
July 8, 2016 at 3:31 am

#311574

Hi,
I appreciate this is quite a broad post.
I do not have admin rights in our SQL 2008 sandpit. I have only read/write.
What are the implications of this on tuning my queries/indexes etc. Are there any workarounds?
It is quite frustrating not being able to view things such as execution plans or the sys tables for missing index group...
Any suggestions welcomed.
Q

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

If the admin of the server won't increase your privileges, I think you have no choice but to set up your own instance on which you can do what you like. Next step - persuading them to share the code with you so you can recreate the environment for yourself!

John

quentin.harris SSC Eights! Points: 924 More actions · Answer 2

Are there possibilities where they can extend rights within a specific database in the datawarehouse and/or is this all about how permissions are set up at the top level and flow down?

It is only one database i need to be able to check performance in.

Q

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

John Mitchell-245523

SSC Guru

Points: 148809

July 8, 2016 at 3:50 am

#1889124

Yes. Have a look at this.

John

quentin.harris SSC Eights! Points: 924 More actions · Answer 4

Awesome. Though i will be seeing flying pigs before i think i will be allowed to do this as it seems it can show sensitive information such as passwords. 🙁

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

quentin.harris (7/8/2016)
Awesome. Though i will be seeing flying pigs before i think i will be allowed to do this as it seems it can show sensitive information such as passwords. 🙁

Show passwords? Really? Or are you storing passwords in plain text?

Thomas Rushton
blog: https://thelonedba.wordpress.com

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Only if the queries that are being run are rubbish enough to include passwords in them. But it comes down to this: they either trust you to do your job, or they don't.

John

quentin.harris SSC Eights! Points: 924 More actions · Answer 7

True. It is a difficult place to be as a developer not sitting within the DBA/BI team but interfacing with their DW.

It seems to be a life of workarounds................................................................

quentin.harris SSC Eights! Points: 924 More actions · Answer 8

@thomas - This was in reference to a linked article which states that:

"Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords."

I have been trying to find a way that i can optimize/tune my queries where i don't have full admin rights.

Q

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Yes, but that's only if queries contain such sensitive information as passwords. If no queries ever filter on plain text passwords (which they damn well shouldn't), then those can't expose plain text passwords.

To be honest, that warning's more relevant when giving those permissions to someone who doesn't already have read rights to all the tables.

Is this the production environment that you're trying to get access to, or a dev/test environment.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 100942 More actions · Answer 10

To do any proper type of index tuning, you must have access to at least:

missing index stats, via sys.dm_db_missing* views;

index usage stats, via sys.dm_db_index_usage_stats;

index operational stats, via sys.dm_db_index_operational stats.

With that, you can do some tuning even without looking at the queries, although obviously if there are major coding issues in the queries, such as functions in WHERE conditions, you don't be able to find those using just index stats.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

HappyGeek SSCoach Points: 18726 More actions · Answer 11

quentin.harris (7/8/2016)
True. It is a difficult place to be as a developer not sitting within the DBA/BI team but interfacing with their DW.
It seems to be a life of workarounds................................................................

Sometimes it is better to cut and run, blame sits somewhere!

...

Eric M Russell SSC Guru Points: 125519 More actions · Answer 12

quentin.harris (7/8/2016)
Awesome. Though i will be seeing flying pigs before i think i will be allowed to do this as it seems it can show sensitive information such as passwords. 🙁

Neither execution plans, nor SQL Profiler traces, nor Extended Event traces show authentication credentials, unless they are embedded within the SQL select statement.

If your DBA doesn't want to grant you show execution plan permission in the same environment where there is a performance issue, then the DBA must be willing to accept that responsibility herself. Any DBA worth a damn knows basic performance monitoring and plan analysis even if she doesn't code T-SQL. If the DBA or management complains to you about a performance issue, tell them that without the proper tools and authborization needed to diagnose the problem where it occurs, all you can offer is your emotional support.

https://www.rickhanson.net/articles/making-a-family/support/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 13

HappyGeek (7/11/2016)
quentin.harris (7/8/2016)
True. It is a difficult place to be as a developer not sitting within the DBA/BI team but interfacing with their DW.
It seems to be a life of workarounds................................................................
Sometimes it is better to cut and run, blame sits somewhere!

Heh.... First, get 3 envelopes...

http://wikibon.org/wiki/v/Prepare_three_envelopes

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric M Russell SSC Guru Points: 125519 More actions · Answer 14

Jeff Moden (7/12/2016)
HappyGeek (7/11/2016)
quentin.harris (7/8/2016)
True. It is a difficult place to be as a developer not sitting within the DBA/BI team but interfacing with their DW.
It seems to be a life of workarounds................................................................
Sometimes it is better to cut and run, blame sits somewhere!
Heh.... First, get 3 envelopes...
http://wikibon.org/wiki/v/Prepare_three_envelopes

I recall one occasion when troubleshooting a stored procedure in production written by a developer who had recently quit. After I finally narrowing down the problem code block, the "comments" included some advice that was obviously an attempt at humor by a guy who had at the time of writing decided to move on to other things.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho