Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to Do When You Inherit a Database
Couple of weeks ago started on a new job. I'm supposed to be working as BI analyst and SQL Developer. But right on the firsts assignments noticed in general everything was taking long to execute. Asked the person that is guiding me on the first days, my supervisor you could say, and he told me that they know the databases are a mess. Asked if I could take a look and see what could be done, got a yes as answer.
So I began to digg in, using several really handy scripts, like for example:
- sp_whoisactive from Adam Machanic
- sp_blitz from @BrentOzar
- sp_BlitzIndex also from @Brent Ozar
- sp_IndexAnalysis from Jason Strate
- 2n edit Ola Hallengren maintenance script
- ...and some others to get a picture of what is going on...
What I've found is a big mess as they told me. As an example, blitzindex procedure returns almost 2000 rows with lot of duplicate indexes, NC indexes including all the columns from a table, lot of heap tables, really wide indexes and more. As for backups, none is done since several weeks, asked about it and IT guys just copy the databases each night to a different server. Couple of databases are over 100Gb and several others are close to that size too. Statistics are updated everyday for every table. There are reports that take more than hour to finish, on not so big tables (just couple of millions of rows). And so on.
As a test I spent couple of days tuning couple of big tables and different procedures and queries that use them. Prepared a baseline, using the profiler. Then made few changes and ran again the test queries. As expected, a report that was taking about 8min now is running in around a minute and a couple of other queries also now take less than half the time. All these changes are done on a test server, we still have a reporting server and a production server.
Taking into consideration that I'm supposed to be a BI and sql developer with limited rights new on the office and not a DBA. What other actions do you recommend me to do in order to approach this scenario? There is an appointed DBA but seems to be just an sql developer doing some dba tasks. There was DBA but he left around half year ago they told me. Should I forget about these issues? Or as someone that is heavily using the database I must point out the problems and propose solutions? Has someone been on the same scenario?
Thanks everyone for the comments and answer. It's been one month since I posted the question. So now I can point out more accurate issues.
I believe indexes is one of the main concerns, but deleting indexes is completely forbidden, at least for now. Even when there are critical cases with several identical indexes, as on one of the main tables, with around 30million rows and growing, there are 3 NC indexes with 7 columns indexed and including all the columns. I can create indexes under very strict supervision but nothing more.
There are several disabled indexes, do they affect the performance on CRUD operations?
The "Uber view" syndrome. Lots of views inside another view inside another one. Some advices regarding this point?
Lot of sp that execute fast on SMSS but when calling from SSRS take several minutes to execute. Reading about it found that seems that is an issue related to parameters sniffing and their use on the stored procedures. One recommendation is to use variables on the sp. I already tried this on couple of them, with successful results. Also some recommend using
WITH RECOMPILE. But as I understand, this can be counterproductive as each time a new execution plan needs to be created on recompilation. As always, I guess it "depends", must test and see where it can be helpful and where not. Any other suggestion?
Backups, logs, etc, is out of my hand for now. So I must center on performance and optimization issues.
There are also lot of duplicated sp's, udf's tables and some other objects. I guess that where created at some point on some schema, usually dbo. But then, after a while, a new schema was created to accommodate some of the objects but the old ones on dbo where not deleted. So now when modifying things I'm encountering lot of duplicated (some times triplicated) objects on the database.
Is there an easy way (checking dependencies maybe?) to check which object are still on use so I can make a report of which ones should be deleted?