Below are answers to questions asked during the recent webinar. If you have additional questions, please contact Janis Griffin at janisgriffin@confio.com
Q: Which version of SQL server are we going to use for this session?
A: We went over SQL Server 2012. However, Extended Events were introduced in 2008. There is a lot of information on the internet about using Extended Events in 2008. The Evented Event UI in SSMS was introduce in 2012.
Q: Is there a way to move the event file to another drive like we do for tempdb files?
A: Yes, you can edit the system_help session's properties and change the location and sizing of the files underneath the 'data storage' section. However, be careful to allow SQL Server permissions to write to that directory.
Q: How would you resolve issues detected with Extended Events?
A: Depending on the issue, I would look at the system_health system to see if there are enough resources (i.e. cpu / memory) and review the wait info to see what is taking the longest time in the database. You can sort, group and aggregate the information to find the top sqls spending the most time in your database and then have some great info (wait_types) to further understand the issue and/or tune.
Q: A SQL Statement can have multiple wait types. Will it list each SQL_Text and all of its WAITS?
A: Yes, you can group by sql_text and wait types, then sum and sort by duration descending to show all the wait types for a particular sql.
Q: We use SQL Server 2008 R2. Where do I find the system_health tab under Management?
A: The system_health session is installed in 2008, but you can't view it via SSMS. There is a lot of information on the internet to provide 2008 system_health session metrics and a custom way to view events in 2008. The Exended Event Interface in Management Studio was introduced in 2012. Here are a couple good links for 2008: http://blogs.msdn.com/b/psssql/archive/2008/07/15/supporting-sql-server-2008-the-system-health-session.aspx http://extendedeventmanager.codeplex.com/
Q: Is System Heath Extended Events meant to replace the SQL Server default trace? Even in SQL 2014, the default trace still uses the legacy SQL trace events.
A: This link warns of NOT using the default trace as it will be going away. Apparently, they weren't able to remove it from 2014 as they had initially planned. http://msdn.microsoft.com/en-us/library/ms175513%28v=sql.120%29.aspx
Q: What xml editor do you recommend using?
A: I just used the Management Studio editor. If you double-click on an XML column, it will automatically put you in the XML editor
Q: So is Confio going to add some of these to Ignite so it will be easier to diagnose issues?
A: Confio constantly reviews new features and functionality in all of the database types it monitors and enhances the product accordingly.
Q: Can we view IOPs per query/hash/user/database?
A: I have not seen actual IOPs in the system_health session, however you can review IO Wait Types to understand the amount of time you are spending on IO. Additionally, there are other DMVs that give this information. Ignite actually shows IOPs at all of these levels - down to a 10 minute interval.
Q: Actually TVF stands for Table Valued Function,which relates to the table valued function that, as you mentioned earlier, we use to parse and view live data
A: Thanks for that comment because it adds more pieces to the puzzle about the undocumented XE wait types!
Q: Does Ignite work on SQL Server Dev edition?
A: Yes, it can monitor SQL Express as well.
Q: Can the SQL 2012 EE add-in for SSMS work with SQl 2008?
A: Unfortunately no. The EE Editor and events will only show in SSMS if you are referencing a 2012 instance. However, I have another webinar where I show how you can script the exented events in 2012 SSMS and them work through the errors they throw in 2008 until you get them working in 2008. Many of the stored procedures and event names have changed... so basically that is what you have to work through.
Q: Do you have an example which uses the function fn_xe_?
A: Yes, there is a slide with using the table function to view the event_file target in my presentation.
Q: Could you provide the TSQL so that I could add the Errors and Deadlocks to my Confio?
A: Yes, please request of a copy of these slides at events@confio.com. You will see in the appendix all the TSQL that I used.
Q: Does Ignite work on sql 2008 R2?
A: Extended events first came out in 2008 and Ignite will monitor all versions from 2000 and up.
Q: Are there an extended events that could point you to potential disk issues?
A: I didn't see many in the system_health session. The main one is via the wait_info event where you can look at total_duration of all IO related wait types.
Q: Will the Ignite advisor show information about this?
A: The Ignite advisor will show info on the top 5 sql statements spending the most time in the database. It will show blocking/locking, advice on missing indexes, higher wait times on wait types, execution times or just higher executions than normal.
Q: Can the 2012 client be used to view the events on a 2008 server?
A: Unfortunately no. The EE Editor and events will only show in SSMS if you are referencing a 2012 instance. The extend events in 2012 are not backwards compatable. However, you may be able to reuse some of the 2012 code as mentioned in an earlier question.