Blog

SQL Server with Mr. Denny

Settings of the Query Store GUI

The SQL Server Query Store which was introduced in Azure about a year ago or so, and came to the on-premises version of SQL Server in SQL Server 2016.  This is a fantastic tool which can help you find performance problems.  However when looking at the query store it ma

https://www.flickr.com/photos/mezuni/475143348/in/

https://www.flickr.com/photos/mezuni/475143348/in/

y not be showing you the data that you are looking for. This is because the Query Store GUI doesn’t know what time window you want it to look at by default, so it’s using the settings which Microsoft programmed in as the defaults.  Changing this to get a better view of the data that you have stored within the Query Store is just a matter of changing the settings within the Query Store GUI.

With whatever Query Store report you are looking at within Management Studio in the upper right of the window there’s a “Configure” button.  If you click that it’ll give you the various options for the report that you are looking at.  What those options are will depend on the Query Store report that you are looking at.  The settings I end up changing the most usually fall into the “Time Interval” category so that I can change the time windows that the Query Store is querystoreconfiguredisplaying data from.

For example on the Regressed Queries report Management Studio will default to showing queries from the last hour that have regressed from what they were over the last month. However if I’m looking at a customers system I may want to see the queries that have changed in the last week compared to the last few months.  This is where I’d set those settings.

Once done click OK, and you’ll get updated reports with the data that you are looking for.

Nice and easy.

Denny

The post Settings of the Query Store GUI appeared first on SQL Server with Mr. Denny.

Recommended reading from mrdenny for December 2, 2016

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did. Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny

The post Recommended reading from mrdenny for December 2, 2016 appeared first on SQL Server with Mr. Denny.

My VMware AirWatch Transaction Log File Keeps Growing

Anything the uses a database needs to have the database settings configured correctly, this includes all the VMware tools.  I’ve had a couple of customers contact me recently about VMware’s AirWatch system because the transaction log on the database keeps growiairwatchng.  The reason in both cases so far is because the database is using the default settings and the transaction log was never backed up.  I talked about this at Microsoft’s TechEd conference a couple of years ago during my talk titled “Introduction to SQL Server Management for the Non-DBA”.  If you prefer a VMware themed slide deck, I’ve given basically the same presentation at VMworld as well (registration required).

Fixing this is actually a really simple fix.  You need to do one of two things.

  1. Change the recovery model from FULL to SIMPLE.
  2. Start taking transaction log backups of the database

Now, how do you select the one of these that you want to do?  That’s pretty easy.  Do you need point in time recover-ability of the database?  If the answer to that question is yes, then select option 2.  If the answer to that question is no, then select option 1.

With most AirWatch systems you don’t really care if the database is restored to what it looked like yesterday or 5 minutes ago, so you usually don’t need point in time recovery.  So just change the database from FULL to SIMPLE and you’re good to go.  Doing this is actually really easy.  Connect to the SQL server in SQL Server Management Studio.  Right click on the database and select properties.  Change the recovery model from FULL to SIMPLE, then click OK. That’s it the log won’t grow any more.

If you need to reclaim the space because the disk is full, then you need to shrink the log file.  To do this, click the “New Query” button at the top of management studio and run the following command.

DBCC SHRINKFILE (2, 1024)

That’ll shrink the file down to 1 Gig and it “shouldn’t” ever grow any larger than that. There’s no outage to doing this, and no risk to the system.  Because of the way that shrinkfile works you may need to run it a couple of times over a couple of days in order to actually get all the space back.

If you don’t have SQL Server Management Studio, or you can’t find the properties, here’s a script that’ll do everything that I’ve described.  Just run this in the AirWatch database (it could be named anything so you have to change the database name in the USE statement from YourDatabaseName to whatever the name of your AirWatch database is).

USE YourDatabaseName
GO
declare @dbname sysname
set @dbname = db_name()

declare @sql nvarchar(1000)
set @sql = 'ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE'

exec (@sql)
GO
CHECKPOINT
GO
CHECKPOINT
GO
DBCC SHRINKFILE (2, 1024)
GO

Denny

The post My VMware AirWatch Transaction Log File Keeps Growing appeared first on SQL Server with Mr. Denny.

Why Are Nested Views Horrible?

https://www.flickr.com/photos/1024/3407840726/

https://www.flickr.com/photos/1024/3407840726/

Nesting views in SQL Server by itself probably won’t introduce performance issues. However when it will do is make it MUCH harder to find performance problems. When you have the top view calling 3 views, which each call 3 views you suddenly have a nightmare of views to unwind to resolve the performance issues.

There’s another dirty little secret of views. Even if you aren’t accessing columns from all the tables in the views which are being referenced, SQL Server still needs to query those columns. This increases IO for your queries, for basically no reason. Because of this reason alone it often makes sense to not use views, and specifically nested views.

Now there are plenty of reasons to use views in applications, however views shouldn’t be the default way of building applications because they do have this potential problems.

While working with a client the other week we had to unwind some massive nest views. Several of these views were nested 5 and 6 levels deep with multiple views being referenced by each view. When queries would run they would take minutes to execute instead of the milliseconds that they should be running in. The problems that needed to be fixed were all indexed based, but because of the massive number of views that needed to be reviewed it took almost a day to tune the single query.

Eventually the query was tuned, and the query was able to be run in about 200ms, but the cost to the customer was quite high.

Use views when they make sense, but don’t use them every time. Like everything else in SQL Server, there is no one right answer for every problem. They are one solution for a subset of problems, not the end all solution.

Denny

The post Why Are Nested Views Horrible? appeared first on SQL Server with Mr. Denny.

1 2 3 317

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.