Có 100+ tài liệu thuộc chủ đề "SQL căn bản"
tailieu.vn Xem trực tuyến Tải xuống
Figure 4.5: Physical Disk performance object in Perfmon.. A quick peek at Perfmon, see Figure 4.6, shows the current absence of activity prior to executing a hefty query.. Figure 4.6: Perfmon low disk activity.. Figure 4.7 shows the BCP bulk insert process running.. Figure 4.7: BCPing data into the All_Books_Ever_Read database.. Now for the bad news: Figure 4.8 shows how...
tailieu.vn Xem trực tuyến Tải xuống
Figure 4.14: Minimal log file growing with data load.. This time there were no Auto Grow events for the data file, and only 20 for the log file. Read_ID , on which to place the clustered index. We can now create the clustered index on this Read_ID column, as shown in Listing 4.1.. CREATE UNIQUE CLUSTERED INDEX [Read_ID] ON [dbo].[Book_List....
tailieu.vn Xem trực tuyến Tải xuống
Also, it should be noted that users will be unable to connect to the Book_List table for the duration of the index build. Essentially, SQL Server has to physically order those millions of records to align with the definition of the clustered index.. sp_spaceused tell me now? See Figure 4.17.. Figure 4.17: Building the clustered index has increased the index_size...
tailieu.vn Xem trực tuyến Tải xuống
Figure 4.23: Addition of covering indexes leads to an efficient index seek operation.. So, while indexes do indeed take space, this space utilization is usually more than warranted when they are used correctly, and we see the desired pay-off in query performance.. The issue with indexes arises when development teams adopt a scattergun approach to indexes, sometimes to the point...
tailieu.vn Xem trực tuyến Tải xuống
CREATE TABLE #fixed_drives. #fixed_drives.Drive Group by Drive,. Example results of the Size query are shown in Figure 4.26.. In this chapter, I have explored some of the scenarios where disk space is consumed by processes, in many cases because of incorrect configurations for recovery models, data growth for large objects and queries that overtax TempDB resources. In the next chapter...
tailieu.vn Xem trực tuyến Tải xuống
Who is running the query and from where?. What is the query doing?. Who is running the query?. What you may also find is that the query is issued by an application residing on another server, potentially a web server, in which case the ProgramName field from the sp_who2 results will likely show ".Net Client". DBCC: What is the query...
tailieu.vn Xem trực tuyến Tải xuống
Figure 5.4: Killing the Bad Query process (51).. After killing a SPID you can issue the KILL command again with this option and get a status of how long SQL Server estimates that a ROLLBACK will take. Figure 5.5: SPID that will not be killed.. This situation can occur for the reason that I foreshadowed earlier: the blocking process has...
tailieu.vn Xem trực tuyến Tải xuống
Up to this point we have used sp_who2 to seek out SPIDs that are causing blocking issues, DBCC INPUTBUFFER to elicit the SQL being executed by such a blocking SPID, and then sp_lock to discover some information about the locks being acquired by the offending process. With sp_lock , the underlying system table is syslockinfo . When developing the query,...
tailieu.vn Xem trực tuyến Tải xuống
Figure 5.11: Blocked SPIDs found using Automated Discovery query.. You might decide that you would like to take this query, and make it into a stored procedure. You can then load it into a maintenance database on each server so that you have it always available. It also means that you can parameterize it to control its behavior. For example,...
tailieu.vn Xem trực tuyến Tải xuống
In this chapter, I will describe how I use monitoring tools and techniques to make sure that my Blackberry will always buzz whenever a backup fails, a disk drive fills up, or a rogue process is threatening the performance of a SQL Server.. When the inevitable happens, and the e-mail notification hits your mobile device, probably at some awful hour...
tailieu.vn Xem trực tuyến Tải xuống
Figure 6.2: Database Mail account settings associated with the Notifications profile.. Having configured Database Mail with a default profile and account, both tasks thankfully having guided wizards, you can send a test mail using the stored procedure, sp_send_dbmail. The options for this stored procedure are many but a simple test can be performed with the code shown in Listing 6.1.....
tailieu.vn Xem trực tuyến Tải xuống
An important point to note about the backup code is use of the Mailto_Onerror. This tells the Red Gate extended stored procedure to use its own native SMTP client to send an email notification, if there are any errors with the backup. BACKUP DATABASE , I would capture any errors in a variable string and then email the failures when...
tailieu.vn Xem trực tuyến Tải xuống
Figure 6.11: Native SQL Alerts samples.. As an example of a typical performance alert, I am going to configure an alert in SQL Response to monitor CPU utilization and then run a query that should push the CPU utilization above a set threshold. Figure 6.12 shows the SQL Response alert configuration window.. For this demonstration, I have customized the standard...
tailieu.vn Xem trực tuyến Tải xuống
Figure 6.15: Enabling the capture of trace data.. Figure 6.16 shows the query that SQL Response collected with the alert, which is indeed the query that I ran to trigger the high CPU utilization alert in the first place.. Figure 6.16: Capturing the query that triggered high CPU.. Figure 6.17 shows the SQL Response alerts for Disk Space and stopped...
tailieu.vn Xem trực tuyến Tải xuống
However, without some investigation, we may not be able to give you a granular description of the data or tell you exactly where it is stored ("social security numbers are stored in the SSN field on table X". it is a heavy burden to bear. Knowing all of the residences, even temporary, of the data is vital. The introduction of...
tailieu.vn Xem trực tuyến Tải xuống
I believe, though, that this caveat, which really is just best practice anyway, is not enough to stop you from pushing for access via Windows groups, where you can.. Returning to Table 7.1, we see that there are five rows that correspond to Windows Groups. Two of these are created during the installation of SQL Server, one for the SQL...
tailieu.vn Xem trực tuyến Tải xuống
WHEN usu.isntuser=1 then ''Windows Domain Account'' WHEN usu.isntgroup = 1 then ''Windows Group'' WHEN usu.issqluser = 1 then ''SQL Account'' WHEN usu.issqlrole = 1 then ''SQL Role'' END as Account_Type. ([?]..sysmembers mem INNER JOIN [?]..sysusers usg ON mem.groupuid = usg.uid) ON usu.uid = mem.memberuid. LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid = lo.sid. (usu.islogin = 1 and usu.isaliased = 0...
tailieu.vn Xem trực tuyến Tải xuống
Server1 MSSQLServerOLAPService LocalSystem Server1 SQLAgent$SRVSAT LocalSystem Server1 SQLBackupAgent LocalSystem Server1 SQLBackupAgent_SRVSAT LocalSystem Server1 SQLBrowser LocalSystem Server1 SQLSERVERAGENT LocalSystem Server1 NULL NULL. While I do not use this query often, it always saves me many frustrating minutes of trying to manually find the same information, via tools such as Computer Management and Services.. The queries presented so far have all been...
tailieu.vn Xem trực tuyến Tải xuống
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]. EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(ma x)'). EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(m ax)'). EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(ma x)'). EVENTDATA().value('(/EVENT_INSTANCE/UserName)[1]','nvarchar(max. EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar (max)'). EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(m ax)'). EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(m ax)')
tailieu.vn Xem trực tuyến Tải xuống
on - Used byb sp_trace_setevent to turn on data columns for particular events. Create the trace and store the output in traceError, then test traceError for failure. and alert the user if the trace cannot be started EXEC @traceError = sp_trace_create @TraceID output, 0,. Add events that we want to collect data on for the trace. exec sp_trace_setevent @TraceID, 14,...