Search results matching tag 'Trace'

Express Edition revisited, focus on SSMS

TiborKaraszi — Wed, 30 Jan 2013 17:01:00 GMT

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.)

I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:

In my 2011 post, I never tried to connect from Express SSMS to a non-Express database engine.
I want to check if there are any significant differences in SQL Server 2012 Express Edition, compared to SQL Server 2008R2 Express Edition.

It isn't uncommon that people want to have SQL Server Management Studio (SSMS) on their machines; and instead of searching for the install files for the full product, they download the freely available Express Edition and install SSMS from there. This was the main reason for this update post, and the reason I focus on SSMS and the tools in this post.

It turns out that both 2008R2 and 2012 RTM Express editions of SSMS includes a lot, but not quite everyting that the full version of SSMS has. And they don't have Profiler or Database Engine Tuning Advisor. 2012 SP1 Express download does indeed have the Complete tool package.

Basic and Complete
The full SSMS (etc.) is referred to as "Management Tools - Complete". This is only available with the Product you pay for and with 2012 SP1 Express. The only one available with the various free Express downloads (prior to 2012 SP1), is called "Management Tools - Basic". You can explicitly request to install Basic from an install media that includes Complete, but you have to explicitly request that in the setup program. You don't want to do that.

One difference between 2008R2 and 2012 is when you install from a pay-media and select that you want to install Express. For 2008R2, you then only have SSMS Basic available. For 2012, you have Complete. In other words, if you use a 2012 pay-media and select Express to install SSMS, you have the option to have the full-blown SSMS - Complete (including other tools, like Profiler).

The downloads
For SQL Server 2008R2, you have "Express Edition" and "Express Edition with Advanced Services". The former is basically only the database engine, where the later has some Tools (SSMS Basic, primarily). See my earlier blog post for more details about 2008R2.

For 2012, there are bunch of downloads available. Note that if you want Complete tools, you need to download SP1 of the installers. You find SP1 here (and RTM, which you don't want to use, here). SP1 includes Complete tools, and you will see that those downloads are significantly larger compared to RTM. It isn't obvious what each exe files stand for, but scroll down and you will find pretty good explanations. I tried several of these (SSMS only, Express with Tools, Express with Advanced Services). They all have in common that for RTM the tool included is Basic, where for SP1 we have Complete.

So what is the difference between Basic and Complete?

In the table below, my focus was on what isn't in Basic. In general, I don't bother to list functionality which is available in both Basic and Complete. So, if the functionality isn't in the table below, it is likely available in Basic. I might have missed something, of course! And my main focus was on SSMS and the database engine.

Component/Functionality	2008R2	2012 RTM	2012 SP1
Functionality in SSMS
Node for Agent	Y	Y	Y
Graphical Execution Plans	Y	Y	Y
Projects and Solutions	N	Y	Y
Maint Plans, Wizard	Y	Y	Y
Maint Plans, New, designer	N (1)	N (2)	Y
Maint Plans, Modify	N (1)	N (2)	Y
Node for SSIS Catalog	N/A	Y	Y
Tools menu, Profiler	N	N	Y
Tools menu, Tuning Advisor	N	N	Y
Connect Object Explorer to:
Analysis Services	N	N	Y
Reporting Services	N	N	Y
Integration Services	N	N	Y
Tools
Profiler	N	N	Y
Database Engine Tuning Advisor	N	N	Y

(1): The selections are there, but they were dead - nothing happened when you select them.
(2): The selections are there, but I got an error message when selecting any of them.

Using Qure Workload Optimizer for SQL Sentry

AaronBertrand — Thu, 21 Apr 2011 11:50:00 GMT

Last week at SQL Bits #8 in Brighton, England, SQL Sentry (my employer) announced its partnership with DBSophic, makers of the Qure workload management suite. Qure Workload Optimizer is a tool aimed at analyzing a workload and providing suggestions to improve the overall performance of that workload. These suggestions can range from adding and dropping indexes, to changing schema, to completely re-writing queries and batches. How does Qure differ from other products out there aimed at improving performance? Qure works against a copy of your production database, makes actual changes, and tests the workload against those changes to make sure they lead to better performance - leading to the absolute best recommendations that have actually been proven to improve your workload. If you aren' already excited about the potential here, I suggest you read this paragraph again. :-)

You can read more about the partnership in this press release from April 7th and also in the following blog post by Greg Gonzalez (twitter): SQL Sentry v6.1: Something Old, Several Things New.

The way that the base Qure product currently works is as follows:

on the production server, you create a trace using a template provided by DBSophic, preferably covering a complete business cycle;
on a server dedicated for the analysis, you restore a backup of the production database;
once your trace is complete, you point Qure at the trace files (or table) and to the backup of the database, and run the analysis.

In the bundled version created exclusively for SQL Sentry, you bypass step 1 and simply point Qure at the SQL Sentry v6 repository that has been monitoring your server. So instead of having to set up a trace and run it yourself, you can use what SQL Sentry is already doing for you. Note that this is limited to the last 100,000 trace events, but will also include up to the last 100,000 QuickTrace events as well - so if you have been running QuickTraces on your server, this data will be factored in as well.

Before starting, it's a good idea to have SQL Sentry cover a complete business cycle with the "Top SQL" duration threshold tuned down to something lower than the default (5 seconds). Essentially you want to include as many events as possible; this way, the impact of any recommended changes can truly be tested. Even if an operation currently only takes 2 seconds, a change to benefit some other query might adversely affect some of these shorter queries.

That said, here is a step-by-step flow of how this works. (Click on any screen shot to show full size.)

When you launch the program, you will see the splash screen looks a little bit different:

Qure Workload Optimizer for SQL Sentry splash screen

Next, you'll choose "Start New Analysis" and you will be asked if you want to perform a full analysis or a trial analysis. For our purposes here, let's use a trial analysis. This will mean a limited set of recommendations will be provided (but I will show the results of a fully licensed analysis).

Selecting a trial analysis

Next, you will be asked where you want to store the analysis file. By default this will go under the Qure folder in My Documents, but be aware that this file can be up to 4GB - so if your C:\ drive is getting skinny, you may want to choose a different location:

Selecting a location for the analysis file

You will then be asked for the server where your database has been restored. There is an additional warning here that you should not be performing the analysis against a production database (and I strongly recommend you heed this warning). Please make sure this is a *COPY* of your production database and that the server is not a production server. I feel like I'm repeating myself, but it is *extremely important* to avoid running such an analysis against your production environment. The reason is that changes are made to the database in order to validate the recommendations being made will, in fact, improve performance.

Selecting the server that holds a *copy* of your production database

Once you connect to a server, Qure will ask you which database(s) you want to analyze. You can select more than one (for a full analysis, you can select three; for a trial analysis, two), but in this case we will just select a single database:

Selecting the database(s) to analyze

The next screen asks you for your trace sources. In the base product, you can select any number of trace files, trace tables, or both, but in this custom version of Qure, you go down to Add Trace > Add SQL Sentry Trace:

Adding a SQL Sentry Trace

You will then be asked to point to the SQL Sentry v6 repository, where Qure will analyze the trace data that has already been collected:

Selecting the SQL Sentry v6 repository

On the next screen, there are a couple of options to consider. One that I strongly recommend you select is the option to "Keep all existing indexes." The reason is this: no matter how long you have been collecting trace data or performance metrics, there is always the chance that an index you or software thinks is unnecessary will actually be needed tomorrow. My official advice here is to never drop an index unless you are absolutely sure it is redundant or no longer needed - and never let software do this automatically. Fellow MVP Paul Randal (twitter) recently published a horror story about a tool that recommended dropping "redundant" indexes without knowing the whole story (and failing to recognize the attributes that made the indexes different).

On the other hand, if you want to see what redundant indexes may be suggested by the tool, it is safe to leave this option unchecked. (Since it knows the workload may not represent a full business cycle, Qure Workload Optimizer will not, in fact, recommend dropping indexes based on use or lack of use - it will only recommend dropping duplicate, redundant, hypothetical or disabled indexes. The "Keep all existing indexes" checkbox here dictates whether redundant indexes are considered - all others will still recommend to be dropped.) When you need to be careful is when you get to the point of applying the recommendations, that you review that involve dropping indexes, and be sure that you want to accept those individual changes (you can easily reject any single recommendation).

Analysis settings screen

The next screen is just a summary; you can click the Start Analysis button to get things going:

Summary / Start Analysis screen

You can watch the process as it analyzes the server, database(s) and individual objects, and comes up with recommendations:

Generating recommendations...

You will also see it perform a banchmark analysis, testing the performance before and after the recommendations are applied (don't worry, these changes are reverted):

Benchmarking...

Once finished, you will arrive at a success dialog:

Summary / View Report screen

When you click "View Report," you will be shown a screen which summarizes the recommendations. From here you can get a very quick view of the top batch improvements and how the improvements are measured (duration, I/O, CPU, or some combination):

Top Improved Batches

If you scroll down on that same dialog, you can expand the "Recommendation Summary" to see a high-level view of what types of recommendations are being made, and what kind of impact they will have. Note that not all recommendations are deemed "applicable" - those that are applicable can be applied with minimal risk to behavior, and through scripts generated by Qure Workload Optimizer. The remaining recommendations are provided for guidance and will not be applied automatically - they should still be reviewed, as we'll see on upcoming screens.

Recommendation Summary

If you switch to the Batches tab, you can see exactly how the recommended changes will affect each batch in terms of the overall workload - be it duration, physical or logical reads, writes, or CPU. You can sort here to focus on the batches with improvements in your area(s) of largest concern, for example if your system is very I/O-bound, you may want to focus on the changes with the greatest improvements in I/O, so sort by the physical reads column. Note that you may see cases where some attributes were affected negatively (e.g. physical reads went up), but overall duration went down. You can select individual or multiple batches here, or even select all to see the total impact of all recommended changes.

Optimization Result - Performance

Next, you can move to the Recommendations tab. Here you can review all of the possible changes, and indicate which you want to test by highlighting them and clicking "Accept." Note that the following screen shot shows several recommendations that require manual rewrites of batches; you can't apply these directly to the database, since these were ad hoc queries submitted to SQL Server. But it can give you a good idea about how to improve the code you're sending that way... the example I've pointed out is actually part of my "Mishandling Date Range Queries" set of bad T-SQL habits to kick - applying a function to a column in a search predicate, preventing the use of an index.

Recommendations - Script Review

You can change the filters on the left to highlight specific recommendations; once you have chosen which recommendations to accept, you can move to the Optimize tab, which will guide you through applying the changes (again, only to a *copy* of your production database). When you are happy with the changes you are about to apply, press the "Start Optimization" button:

Optimize screen

This will start the "Apply Recommendations Wizard" - which again has sensible warnings about not applying these changes to production databases:

Apply Recommendations Wizard

You will be asked again for the connection details of the copy / test database, and then you can choose "Start Applying":

Select *copy* of production database to affect

When finished, you will see the following dialog (but don't forget that there are several changes Qure Workload Optimizer recommended but couldn't make automatically, such as changes to batches that are issued ad hoc or from applications):

Success dialog

Now you can test your changes in your test or QA server(s) and deem whether they are satisfactory for migration to your production environment. You can watch the newly changed database(s) on the test / QA server with SQL Sentry v6, run applicable workloads against the test server, and you should find a dramatic improvement in performance.

Once again, as a reminder, please be sure to thoroughly test changes suggested by any person or piece of software, especially in cases where the whole story may not be known - for example, if your collection has not covered a complete business cycle, or if you have filtered out a large number of individual events. A suggestion can only be as good as the information it's based on.

So if you are looking for a reliable and effective way to improve performance of your applications, you should absolutely consider taking Qure + SQL Sentry for a test drive! The software will be available for download soon. In the meantime, if you're not already using SQL Sentry, you can register for a trial download at http://www.sqlsentry.net/download-trial/trial.asp.

Connect digest : 2009-06-20

AaronBertrand — Sat, 20 Jun 2009 15:11:00 GMT

Here are the Connect items I'd like to draw your attention to this week. I have abandoned the idea of trying to keep track of vote counts and overall rating. Being lazy might be a small part of it, but it is mostly because I just don't think it makes sense to assume that my postings are all that influence Connect behavior. I know I have some impact on the visibility of certain items, but I'm going to leave it at that.

====================================

SQL Server 2008 connection pooling problems

Something to watch out for if you are using connection pooling and SQL Server 2008:

#468478 : SQL Server 2008 Periodically Does Not Accept Connections

====================================

Statistics on partitions

As partitioning becomes more of a viable and beneficial option, new requirements are creeping out of the woodwork, such as the ability to update statistics per partition instead of for the entire object:

#468517 : Update Statistics at the partition level

====================================

DBCC SHRINKFILE() flexibility

While personally I think the need is not all that dire, some people want to be able to shrink a database file to a size smaller than the original allocation (which is currently prevented by SQL Server). Joe Sack made the request formal:

#467285 : Shrink files beneath originally allocated space

====================================

Distributed transactions

Erland has filed a couple of interesting issues regarding distributed transactions:

#466739 : There should be a system function to tell whether the current transaction is a distributed transaction

#466749 : Cannot call stored procedures when a distributed transaction has been rolled back

====================================

Clickable URLs in error messages

Finally, Adam is asking for URLs in the messages pane (e.g. those you could embed in custom messages or ad hoc within RAISERROR) to be clickable. I think it makes a lot of sense, though it is currently resolved as "won't fix"...

#454907 : Make URLs clickable in the SSMS Messages pane

====================================

Please remember, I am not trying to coerce you to vote for issues you don't care about, just trying to raise awareness for some items that might have slipped under your radar...

Match those types!

TiborKaraszi — Tue, 28 Apr 2009 10:30:00 GMT

This is a recommendation I believe is worth repeating from time to time: Make sure you match data types when you write TSQL code. Else you in most cases end up with an implicit data type conversion. And in worst case, this conversion is performed at the column side - not the literal side of your query. What does that mean? Consider below:

WHERE Col = Val

Now, say that the types for above don't match. Val might be some parameter (to a stored procedure, for instance), a variable or a written value (literal). In any case, when SQL Server need to do some operation )like comparison like here) involving several values which aren't of the same type, then one of the values need to be converted so it is of the same type as the other. Which one is determined by the rules for "Data Type Precedence".

What we don't want is a conversion at the column side. Why? I would argue that an implicit conversion in the first place in many cases mean I misunderstood something about the types in my system and am on my way of producing a bug in my code. But having a more strict language is not the topic for this particular post (check out this). My point here is that it is bad for performance reasons. Just yesterday and today I was involved in a thread on the MSDN forum. Here's the repro script from that thread (slightly adjusted by me):

USE tempdb GO IF OBJECT_ID('t') IS NOT NULL DROP TABLE t GO CREATE TABLE t ( c1 int IDENTITY(10000001,1) NOT NULL ,c2 char(8) NULL ,c3 datetime NULL ) GO INSERT INTO t(c3) SELECT TOP 3000000 '20080203' FROM master..spt_values t1 CROSS JOIN master..spt_values t2 CROSS JOIN master..spt_values t3 UPDATE t SET c2 = CAST(c1 AS char(8)) CREATE UNIQUE CLUSTERED INDEX x ON t(c2) GO SELECT * FROM t WHERE c2 = N'10000009' SELECT * FROM t WHERE c2 = N'10000008'

Don't bother too much about the first section. The relevant part here is that we have char(8) column with values which are unique (enforced by a unique index), on a table with three million rows.

Now, before we dig deeper: What is the problem with above queries? The type do not match! The type in the table is char(8), but literal is Unicode (the N before the string). Since nchar has higher precedence than char, then the column side needs to be converted to the value side. Not good. Try the code on SQL Server 2000 and you will see disaster. For me, STATISTICS IO gave 10755 logical reads for both queries, with Clustered Index Scan (which is the same as table scan). Hoover on the Clustered Index Scan icon and you will find "WHERE:(Convert([t].[c2])=[@1])". See how the conversion is done at the column side? This, my friend, mean that SQL Server cannot seek an index to find the rows. Ouch! That's just because we were a bit sloppy... Compare to below:

SELECT * FROM t WHERE c2 = '10000009' 
SELECT * FROM t WHERE c2 = '10000008'

You will find for both two above we have Clustered Index Seek with only three logical reads. That is a good improvement.

OK, enough about SQL Server 2000, lets discuss 2008/2005. At first I couldn't see any difference between the two original queries (N'10000009' and N'10000008'). Both seemed to be instant, and the same execution plan for both. I even saved the execution plan as XML and used WinDiff to compare the xml files. No difference. But the poster said the "9" query was slower than the "8" query. I executed to text and had ctrl-E pressed for a while. And now I could see a difference. The flicker was quicker for the "8" version vs. the "9" version. I used Profiler and confirmed. The "9" version had consistently about 90000 microsecond duration where the "8" version had about 0 duration. This was also reflected in CPU usage, as shown by Profiler. I hoped to use Extended Events to find something deeper about this, but that didn't give me anything. Perhaps I didn't capture the relevant events, quite simply. X/Event experts are welcome to suggest event session configurations. :-)

My guess for the difference is in the execution plan. You will see an index seek for both. That is good, since SQL Server nowadays potentially can use an index even if you end up with an implicit conversion at the column side. But you will see a few more operators in addition to the index seek. See that Constant Scalar followed by a Compute Scalar? My guess is that the built-in internal function used inside the Compute Scalar operator is more expensive for N'10000009' compared to N'10000008'. You can see this function if you use SET STATISTICS PROFILE ON. See the GetRangeThroughConvert() function. My guess is that the answer is the cost for this function. (Search the Internet for things like "Constant Scan" and "GetRangeThroughConvert" for other blog post on this topic.)

The moral of the story? Here we have a cost which can be relevant in some cases, but we will only see this if we happen to execute our query with the right values and are very very observant. But we can eliminate all this mess this by using the right type for the column in the first place!

Did we have recent autogrow?

TiborKaraszi — Thu, 19 Jun 2008 13:51:00 GMT

I just read the question how to find out when autogrow in tempdb happened. And again I want to push for the default trace. The simple answer is that you already have this information available, just go and get it! (Unless you explicitly disabled the default trace, of course...)

Adjust the file name for your most recent default trace file in below query:

SELECT te.name, t.DatabaseName, t.FileName, t.StartTime, t.ApplicatioNname 
FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\LOG\log_331.trc', NULL) AS t 
INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id 
WHERE te.name LIKE '%Auto Grow' 
ORDER BY StartTime ASC

Did You Know SQL Server Has A Black Box Like An Airplane?

Denis Gobo — Sun, 03 Jun 2007 19:33:00 GMT

Paul Randal writes:

"Kimberly mentioned that SQL Server has a 'black-box' trace, similar to an aircraft flight-recorder, which I'd never heard of. It's an internal trace that has the last 5MB of various trace events and it's dumped to a file when SQL Server crashes. This can be really useful if you're troubleshooting an issue that causing SQL Server to crash or someone or something is telling SQL Server to shutdown and its unclear who or what is doing it."

Read here how to turn it on: http://blogs.msdn.com/sqlserverstorageengine/archive/2007/06/03/sql-server-s-black-box.aspx