October 17, 2019 at 7:42 am
Is SQL profile being deprecated and extended events should be used instead?
Also, has anyone heard that there is more of a concern around Statistics rather than fragmentation ?
October 17, 2019 at 9:14 am
According to Buck Woody of Microsoft at a talk I went to earlier this year, deprecation is deprecated. What I think he meant is that they're not deprecating features any more, so I imagine Profiler will be there for the foreseeable future. That's not to say that it'll necessarily get the same love and attention that Extended Events does. There are several reasons to prefer XE over Profiler - I think Grant Fritchey has written about this.
Regarding statistics and fragmentation, I assume you're referring to assertions that it's more important to update statistics than to rebuild indexes. You may want to read what Jeff Moden has written about this, since he's done extensive testing and given talks on the subject. My view is that if you have the luxury of designing your own indexes, and you choose your fill factors wisely, then you'll want to keep the indexes as unfragmented as possible. Keeping statistics up to date is still equally, if not more, important, however.
John
October 17, 2019 at 2:33 pm
I don't think that the profiler will disappear soon, but it also will not be develop to support new features. Extended events on the other hand will continue to support new features. Today there are already things that we can do with extended events and we can not do with profiler. For example get information about wait statistics for a specific session, get errors with the specific statement that caused this error, etc'. For this reason I think it is better to know how to use extended events. Personally I use both of them (Profiler and extended events). For simple things such as see what value was passed as a parameter to a stored procedure I'll still use profiler, but for more advance things I'll use extended events.
Adi
October 17, 2019 at 3:28 pm
Why I worry less about fragmentation: there are so many different layers between sql server and the hardware storage if you are running virtualized or with SAN-storage.
Statistics are more important as the query plan and thus performance might depend on them
October 17, 2019 at 6:31 pm
On the subject of index maintenance...
I went for 2.5 years with virtually no index maintenance on my production box. With that I can tell you that it IS more important to keep statistics up to date that to keep fragmentation low BUT... you cannot simply ignore fragmentation.
Now, that being said, I've found out the hard way and through about 400 hours of testing that most of us have been defragmenting our junk incorrectly and that it's better to not defrag at all rather than to defrag incorrectly. Doing things correctly involves a wee bit of work but I can tell you, stop using REORGANIZE. It's been mostly documented correctly as to how it works but everyone mistakes what it says and it causes big problems. And, it's not the friendly little thing that a lot of people think especially but not limited to large indexes. When they say "it's fully logged", them mean it and in spades!
You also need to stop rebuilding indexes with a 0% fill factor. Rebuilding them at 0% is the same as building them at 100% and if you don't know the insert/update/delete pattern of the index, then your index maintenance may (will probably) end up being a major source of blocking on the morning after your index maintenance just like it was for me. If you need to recover some disk space, rebuild indexes that have less than 80% page density at 82% Fill Factor until you know more about the indexes. Even that can be a space waster, though. There are tradeoffs and fixes that are way too long to include in a single post.
There's actually a bunch of rules I'm working on for how to defrag indexes to reduce page splits without wasting a shedload of memory but I've not completed my suggestions yet... especially since "ExpAnsive" updates can put such a twist on fragmentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 17, 2019 at 7:10 pm
As has already been shared, while Profiler/SQL Trace has been deprecated, it really means that no new features will be supported by SQL Trace. For instance as soon as you use features added after SQL Server 2008R2, you won't have trace events for them, but you will have extended events for them.
Extended events allow you to go deeper which sometimes means you have to know more to use them. The other thing to remember is by design, extended events are lighter weight than SQL Trace so have less impact on your system.
I still use both. Profiler is still simpler to use for a quick hit, light-weight trace to see what is going on by with the latest versions of SSMS that have XEvent Profiler and the pre-defined simple Xevent sessions that are very similar to the way most people use Profiler there is less reason to use Profiler.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply