コード例 #1
0
        /// <summary>
        /// Gets or creates an XEvent session with the given template per the IXEventSessionFactory contract
        /// Also starts the session if it isn't currently running
        /// </summary>
        public IXEventSession GetOrCreateXEventSession(string template, ConnectionInfo connInfo)
        {
            string sessionName = "Profiler";

            var sqlConnection             = ConnectionService.OpenSqlConnection(connInfo);
            SqlStoreConnection connection = new SqlStoreConnection(sqlConnection);
            XEStore            store      = new XEStore(connection);
            Session            session    = store.Sessions[sessionName];

            // start the session if it isn't already running
            if (session == null)
            {
                session = CreateSession(connection, sessionName);
            }

            if (session != null && !session.IsRunning)
            {
                session.Start();
            }

            // create xevent session wrapper
            return(new XEventSession()
            {
                Session = session
            });
        }
コード例 #2
0
        private static Session CreateSession(SqlStoreConnection connection, string sessionName)
        {
            string createSessionSql =
                @"
                CREATE EVENT SESSION [Profiler] ON SERVER 
                ADD EVENT sqlserver.attention(
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
                    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
                ADD EVENT sqlserver.existing_connection(SET collect_options_text=(1)
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
                ADD EVENT sqlserver.login(SET collect_options_text=(1)
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
                ADD EVENT sqlserver.logout(
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.nt_username,sqlserver.server_principal_name,sqlserver.session_id)),
                ADD EVENT sqlserver.rpc_completed(
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
                    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
                ADD EVENT sqlserver.sql_batch_completed(
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
                    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0)))),
                ADD EVENT sqlserver.sql_batch_starting(
                    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.query_hash,sqlserver.server_principal_name,sqlserver.session_id)
                    WHERE ([package0].[equal_boolean]([sqlserver].[is_system],(0))))
                ADD TARGET package0.ring_buffer(SET max_events_limit=(1000),max_memory=(51200))
                WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)";

            connection.ServerConnection.ExecuteNonQuery(createSessionSql);

            XEStore store = new XEStore(connection);

            return(store.Sessions[sessionName]);
        }
コード例 #3
0
        public ObjectModel()
        {
            SqlConnectionStringBuilder conBuild = new SqlConnectionStringBuilder();

            conBuild.DataSource         = "(local)";
            conBuild.InitialCatalog     = "master";
            conBuild.IntegratedSecurity = true;
            SqlStoreConnection server = new SqlStoreConnection(new SqlConnection(conBuild.ConnectionString.ToString()));

            xe = new XEStore(server);
        }
コード例 #4
0
        /// <summary>
        /// Gets an existing XEvent session or creates one if no matching session exists.
        /// Also starts the session if it isn't currently running
        /// </summary>
        private static Session GetOrCreateSession(SqlStoreConnection connection, string sessionName)
        {
            XEStore store   = new XEStore(connection);
            Session session = store.Sessions["Profiler"];

            // start the session if it isn't already running
            if (session != null && !session.IsRunning)
            {
                session.Start();
            }
            return(session);
        }
コード例 #5
0
        private static BaseXEStore CreateXEventStore(ConnectionInfo connInfo, SqlStoreConnection connection)
        {
            BaseXEStore store = null;

            if (connInfo.IsCloud)
            {
                if (DatabaseUtils.IsSystemDatabaseConnection(connInfo.ConnectionDetails.DatabaseName))
                {
                    throw new NotSupportedException(SR.AzureSystemDbProfilingError);
                }
                store = new DatabaseXEStore(connection, connInfo.ConnectionDetails.DatabaseName);
            }
            else
            {
                store = new XEStore(connection);
            }
            return(store);
        }
コード例 #6
0
ファイル: SpyMonitor.cs プロジェクト: showplan/ShowplanSpy
        public static async Task Spy(Action <ShowplanEvent> eventFunc, SpyOptions options, CancellationToken token)
        {
            await Task.Run(async() =>
            {
                var sessionName      = $"{XePlanNamePrefix}-{Environment.MachineName}-{Guid.NewGuid()}";
                var connectionString = ConnectionStringBuilder.Build(
                    "master",
                    options.Server,
                    options.UserName,
                    options.Password);


                var store = new XEStore(new SqlStoreConnection(new SqlConnection(connectionString)));

                if (options.CleanOnStart)
                {
                    CleanUpExistingSessions(store);
                }

                var existingCount = store.Sessions.Count(s => s.Name.StartsWith(XePlanNamePrefix));
                if (existingCount > 0)
                {
                    Log.Logger.Warning("Found {count} existing plans that might need cleanup. Run with -c option to remove them.", existingCount);
                }

                var filterExpression = FilterBuilder.Build(options.Database, options.AppName);

                var session = store.CreateSession(sessionName);
                session.MaxDispatchLatency = 1;
                session.AutoStart          = false;

                var showPlanEvent = session.AddEvent("sqlserver.query_post_execution_showplan");
                showPlanEvent.PredicateExpression = filterExpression;
                showPlanEvent.AddAction("sqlserver.sql_text");
                showPlanEvent.AddAction("sqlserver.query_hash");
                showPlanEvent.AddAction("sqlserver.query_plan_hash");
                showPlanEvent.AddAction("sqlserver.plan_handle");

                try
                {
                    Log.Logger.Verbose("Creating new session {session}", session.Name);
                    session.Create();
                    Log.Logger.Verbose("Starting new session {session}", session.Name);
                    session.Start();
                }
                catch (Exception e)
                {
                    Log.Logger.Fatal("Unable to create monitoring session", e);
                    throw;
                }

                Log.Logger.Verbose("Session {session} started", session.Name);

                // if the task gets canceled then we need to break out of the loop and clean up the session
                // we're checking in the loop for the cancellation but that will only hit if an event is triggered
                // so this lets us be a bit more aggressive about quitting and cleaning up after ourselves
                token.Register(() => SessionCleanup(session));

                try
                {
                    using (var eventStream = new QueryableXEventData(connectionString, sessionName,
                                                                     EventStreamSourceOptions.EventStream, EventStreamCacheOptions.DoNotCache))
                    {
                        Log.Logger.Verbose("Watching new session");

                        foreach (var evt in eventStream)
                        {
                            if (token.IsCancellationRequested)
                            {
                                Log.Logger.Verbose("Cancelling sql spy task");
                                break;
                            }

                            try
                            {
                                var sqlEvent = new ShowplanEvent
                                {
                                    ShowplanXml   = (XMLData)evt.Fields["showplan_xml"].Value,
                                    Duration      = (ulong)evt.Fields["duration"].Value,
                                    EstimatedCost = (int)evt.Fields["estimated_cost"].Value,
                                    EstimatedRows = (int)evt.Fields["estimated_rows"].Value,
                                    QueryHash     = (ulong)evt.Actions["query_hash"].Value,
                                    QueryPlanHash = (ulong)evt.Actions["query_plan_hash"].Value,
                                    SqlStatement  = (string)evt.Actions["sql_text"].Value,
                                    PlanHandle    = (byte[])evt.Actions["plan_handle"].Value
                                };

                                eventFunc(sqlEvent);
                            }
                            catch (Exception e)
                            {
                                Log.Logger.Error("Error creating event", e);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    if (!token.IsCancellationRequested)
                    {
                        Log.Logger.Error("Unknown error while monitoring events", e);
                        throw;
                    }
                }

                await Task.Delay(0, token);
            }, token);
        }
コード例 #7
0
ファイル: ExtendedEvents.cs プロジェクト: nani1211/DBHelper
        static void Main()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

            var c = new SqlStoreConnection(new SqlConnection(connectionString));


            Console.WriteLine("Crreate the Session object");
            Console.WriteLine("The most important properties are : the session name and the parent");
            var se = new XEStore(c);

            se.Sessions[SessionName]?.Drop();

            var x = new Session()
            {
                Parent              = new XEStore(c),
                MaxMemory           = 2048,
                EventRetentionMode  = Session.EventRetentionModeEnum.AllowMultipleEventLoss,
                MaxDispatchLatency  = 3,
                MaxEventSize        = 0,
                MemoryPartitionMode = Session.MemoryPartitionModeEnum.None,
                TrackCausality      = false,
                AutoStart           = false,
                Name = SessionName
            };

            Console.WriteLine("Create the target. Be sure that C:\\TMP exists on your computer!");

            var t = new Target(x, "package0.event_file");

            //--== Specify target fields

            var tf = t.TargetFields["fileName"];

            tf.Value = @"C:\Tmp\YourSession_Target.xel";
            tf       = t.TargetFields["max_file_size"];
            tf.Value = 2;
            tf       = t.TargetFields["max_rollover_files"];
            tf.Value = 2;
            x.Targets.Add(t);


            Console.WriteLine("Create the event");
            var e = new Event(x, "sqlserver.sql_statement_completed");


            Console.WriteLine("Create the Action");
            var a = new Microsoft.SqlServer.Management.XEvent.Action(e, "sqlserver.sql_text");

            Console.WriteLine("Predicate expression");
            e.PredicateExpression = "( [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text], N'%SELECT%HAVING%') )";


            e.Actions.Add(a);
            x.Events.Add(e);

            Console.WriteLine("Create the Session");
            x.Create();
            Console.WriteLine("Start the Session");
            x.Start();
            Console.WriteLine("Press any key to exit ...");
            Console.ReadLine();
        }