June 18, 2020 at 2:05 am
I have left my old job but cannot find a copy of the Auto Tuning Index DB TSQL that I used to have on all my servers.
It was written about 10 years ago I believe by Microsoft people and I took the code from their blog. However I cannot find the SQL to rebuild it now.
I am on a laptop, with MS SQL Express running so I cannot use the build in automatic tuning tool. I just want the DMV that holds missing indexes or unused indexes in tables, so that restarts and reboots doesn't loose the data from the DMV's that are cleared when that happens.
I know not to wildly just implement every index recommendation and to cover as many queries with as few indexes as possible. However this is a read heavy system, writing done in the morning, rest is read 90%, Insert/Update 10% til midnight.
I could build one from scratch using the DMVS that used to list the table, the index name, the main columns, included columns and so on but I know there is one out there to save me the time.
It just used to use a job to save current DMV counters of index hits, updates etc in tables so that if MS SQL was rebooted those stats would still be there and over time you can see the most unused indexes from the total count over months etc.
I have had a look tonight but can just get one SQL statement to list me some recommendations, I would like the SQL to rebuild the AutoIndex Recommendation Database.
Thanks for any help in finding it or something similar that saves DMV data on a schedule so it's not lost.
Thanks
June 18, 2020 at 12:57 pm
I honestly don't know what you're talking about. I tried a search for this and nothing came up. I'm not aware of a tool built by Microsoft that does what you're describing. I don't think they would build such a tool. Even they know the missing index suggestions are frequently poor and sometimes downright wrong to the point of being evil. I sincerely doubt they'd have a mechanism that would simply, automatically, create all of them. However, if they did, I'd bet solid money it was created by the Sharepoint team or someone like that, not the SQL Server team.
However, I'll reach out to the people who build the tools at Microsoft to see if they can help.
Strong recommendation though, don't do this. The missing index suggestions are just that, suggestions. Creating all them automatically would be a very poor choice.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2020 at 3:16 pm
As I said in the 1st post I don't automatically implement every recommendation. The DB was designed to just keep DMV data from missing index data over time as when the servers reboot or restart the DMV memory clears.
So it was a small DB designed with some tables to store DMV data and a few procs to get out the full missing index suggestion with included colums etc.
My SQL Express Management Console has an option for Automatic Performance Improvements but its not supported by Express.
Maybe they built that tool from the idea of the decade old DB I used on all my servers to collect stats over months to see which queries were being hit, not used, how many user/system writes and reads etc.
It was just useful to see the indexes the DMVs threw up all the time. Plus as I have scripted my DB down to a laptop I have hardly any indexes. I wanted to run it over a week or two and then work out optimal query coverage.
I don't blindly inplement all suggestions but when you have indexes saying 99% performance improvement if inplemented then its worth taking a look as at the moment I have a BOT running against a DB with lots of tables but hardly any indexes so its taking a while to run.
Wanted to see which queries were causing the slow downs so I could add appropriate indexes and this Auto Index Reccomendation Db has always belped.
Thanks
June 18, 2020 at 3:47 pm
I've reached out to one of the Microsoft dev leads. I'll see what I can find. I pointed them back here. Hopefully someone will swing by.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2020 at 6:25 pm
Rob Reid - If you have the server running for a week or two and you designed the thing, you should have access to the metrics on the server, right? If so, you could manually check the slow running queries and check for missing index suggestions on the tables associated with those queries, right?
Or failing that, I am hoping the bot is calling stored procedures and not just random TSQL. If so, you could look through those SP's and add appropriate indexes.
Not saying that having those metrics captured automatically for you is a bad thing, but if you know which queries are slow, you can investigate them and improve them. Sometimes a slow query is slow because you need an index; sometimes it is slow because you have a cursor; sometimes it is slow because you don't have enough resources. The last one is often true when comparing running it on a server vs a laptop. My servers (for example) have 128 GB of RAM each. My laptop has 6 GB so I expect ALL queries to be slow.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 18, 2020 at 6:27 pm
Thanks,
I am pretty sure I got the code off a Microsoft blog years back when DMVS just came out (2005 or was it 2008? Cannot remember)
Whether they were current MS employees or ex I have no idea. It was purely a DB to log all DMV missing index data permanently as reboot/restart clears DMVs so you won't get accurate results.
However running it for a month with the whole DB you will have tons of records and the worst offenders by % performance gain, or reads, writes, etc is useful especially now I don't have the old indexes on my Express Db and some processes like calls to certain stored procs that do lots of calcluations and inserts, updates are taking 10+ minutes to run on my local Express site but on the old DB with right indexes took seconds.
Any help would be needed and I have a feeling they have automated it in Management Console as the Automatic Performance Tuner tool as that says it will add or drop indexes according to usage data. I can't run it on Express and I don't want my indexes auto added or dropped just the stats so I can decide what to do.
Thanks
June 18, 2020 at 6:36 pm
Hi Brian,
I don't know if you noticed but I had to move the DB from a server to an SQL Express DB on my laptop.
The laptop wont be running 24/7 like a server so I need to store the DMV data periodically so I don't lose it.
I know which stored procs are slow but they contain calls to many other procs and SQL statements. So I'd have to go through each one and run the code with display execution plan on and see the costs and ideas for missing indexes.
However alot of the DB code run I wouldnt know was a cause of a problem apart from the DMV data, which is why I wanted to keep the info inbetween laptop turn offs.
Also SQL Express constantly loses connection to the files and reconnects are required. On each restart all my DMV dats will be lost.
Thanks for your help though.
June 18, 2020 at 7:45 pm
I think our approaches to index creation/removal are a bit different. But if it works for you, that's good!
My thoughts though about performance are less about indexes specifically, but on what is slow? Are you certain that it is lack of indexes that are causing performance issues OR is it the change in hardware. Going from a beefy server with tons of resources to a tiny little laptop may be causing your querys to spill to disk rather than run in-memory.
I'm not saying indexes won't help, I am just suggesting that they may not give you the biggest bang for your buck.
For example, if your stored procedures are using cursors to call other stored procedures in a loop, that's going to be a performance hit. If you have while loops in the code, that's going to be a performance hit. If you are joining large tables that don't fit in memory, that's going to be a performance hit.
If you wanted to capture the missing indexes suggestions at instance shutdown, that shouldn't be too hard to set up. Not sure if SQL has a shutdown trigger, but you could have a process you follow where you capture the data from sys.dm_db_missing_index_details into a table before you do a service stop.
I am also not sure what you mean by SQL Express losing connection to the files and reconnects being required. To me that sounds more like a problem with the install than with SQL Express and might want to investigate that. Do you mean SSMS loses the connection to the SQL instance? If so, my guess is you hare having some sort of resource contention happening and you are timing out.
And not trying to push you away from the scripts you are looking for or to argue, just wondering if indexes are the problem here or if it is something else.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 19, 2020 at 6:11 am
Could it be this?
https://docs.microsoft.com/en-us/archive/blogs/queryoptteam/fun-for-the-day-automated-auto-indexing
The greatest obstacle to transforming the world is that we lack the clarity and imagination to conceive that it could be different. -- Roberto Unger
June 19, 2020 at 10:12 am
PAM!
Thanks for stopping by. That sure looks right to me.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 20, 2020 at 5:43 am
Thanks Pam that is the exact code I was looking for.
Great I can get it running to save DMV index recommendations. At the moment most of my tables have no indexes, maybe a PK, on them at all and it is definitely slowing down the script.
I just don't want to spend time putting the correct one on as I cannot remember them all so this will help.
Thanks for finding it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply