jay
Using SQL Server 2008 Extended Events. By Jonathan Kehayias, SQL Server MVP.
You can view Jonathan’s blog at. Introduction to Extended Events - Using SQL Server Extended Events. For those fighting with all the Extended Event terminology, let's step back and have a small overall introduction to Extended Events.
This post will give you a simplified end to end view through some of the elements in Extended Events. Before we start, let’s review the first Extented Events Objects that we are going to use: Events: The SQL Server code is populated with event calls that, by default, are disabled. Adding events to a session enables those event calls. Once enabled, they will execute the set of functionality defined by the session.Target: This is an Extended Event Object that can be used to log event information. Also it is important to understand the following Extended Event concept Session: Server Object created by the user that defines and configures the Extended Event object to be use to when certain events happen. Introduction to Extended Events – Finding Long Running Queries « Journey to SQLAuthority. The job of an SQL Consultant is very interesting as always.
The month before, I was busy doing query optimization and performance tuning projects for our clients, and this month, I am busy delivering my performance in Microsoft SQL Server 2005/2008 Query Optimization and & Performance Tuning Course. I recently read white paper about Extended Event by SQL Server MVP Jonathan Kehayias. You can read the white paper here: Using SQL Server 2008 Extended Events. I also read another appealing chapter by Jonathan in the book, SQLAuthority Book Review – Professional SQL Server 2008 Internals and Troubleshooting.
After reading these excellent notes by Jonathan, I decided to upgrade my course and include Extended Event as one of the modules. This week, I have delivered Extended Events session two times and attendees really liked the said course. In the following Events, we are writing the details of the event at two locations: 1) Ringer Buffer; and 2) XML file. Reference : Pinal Dave ( SQL Server Technical Paper. SQL Server 2008 DMV.
How to determine the appropriate page file size for 64-bit versions of Windows. The 64-bit versions of Microsoft Windows Server 2003 and Microsoft Windows XP can support more RAM than the 32-bit versions of these products.
When lots of memory is added to a computer, a paging file may not be required. When you use the Pages/sec counter to measure paging file use, the value that is returned may not be accurate. To obtain an accurate measurement of paging file use, you must also use other performance counters. You can use System Monitor measurements to calculate the size of the paging file that your computer requires. Windows Domain Controllers and DFS Replication, Certificate and ADAM/LDS Servers are not supported without a configured pagefile. When you set up a 64-bit version of Microsoft Windows Server 2003 or Microsoft Windows XP, the operating system will create a page file that is one and a half times the amount of RAM that is installed in your computer.
Comparison of memory and CPU limits in the 32-bit and 64-bit versions of Windows. Understanding SQL server memory grant - Tips, Tricks, and Advice from the SQL Server Query Processing Team. This article describes how query memory grant works in Microsoft SQL Server.
It applies to both SQL2005 and SQL2008. Written by Jay Choe, Microsoft SQL Server Engine. Query memory grant (a.k.a. query work buffer) is a part of server memory used to store temporary row data while sorting and joining rows. It is called "grant" because the server requires those queries to "reserve" before actually using memory. SQL Server Central. The DMV for Day 23 is sys.dm_os_process_memory, which is described by BOL as: Most memory allocations that are attributed to the SQL Server process space are controlled through interfaces that allow for tracking and accounting of those allocations.
However, memory allocations might be performed in the SQL Server address space that bypasses internal memory management routines. Values are obtained through calls to the base operating system. They are not manipulated by methods internal to SQL Server, except when it adjusts for locked or large page allocations. All returned values that indicate memory sizes are shown in kilobytes (KB).
Sudarshan Narasimhan « TroubleshootingSQL. Well, this is often a tricky situation, where you are running into Virtual Address Fragmentation and getting OOM (out-of-memory) errors in your SQL Server.
Most often, people do not understand the difference between physical memory pressure vs. virtual memory pressure. Adding more RAM is definitely not a solution here! Here are some sample error messages you might have seen in your SQL Errorlog which will indicate if this is physical memory or virtual memory issue, Page V: SQL Server Architecture. SQL Server ArchitectureWe have already mentioned that SQL Server is quite easy to use, but this can also be one of its drawbacks when it comes to people’s perceptions of this product.
As long as you understand what it does and when it behaves in a wrong way, we argue that there is no value in having to tune a database server manually when you can let the built-in logic do it. You want your database administrators and IT staff spending time on other stuff, like designing, planning, and deploying applications. Obviously such staff would still need to provide maintenance and surveillance of your existing SQL Server system; if it behaves strangely, they must have the knowledge to troubleshoot it.
This should not be their main occupation, however. A database that requires IT staff to spend hours and hours just to get it up and running takes too much time from the IT staffs' other tasks. How to reduce paging of buffer pool memory in the 64-bit version of SQL Server. Microsoft SQL Server performs dynamic memory management based on the memory requirements of the current load and activities on the system.
On a Windows Server 2003 or a Windows XP or later version system, SQL Server can use the memory notification mechanisms that are provided by the QueryMemoryResourceNotification Windows API. On a Microsoft Windows 2000 Server-based system, SQL Server periodically calculates the free physical memory on the system by using the native Windows API. Based on this information from the QueryMemoryResourceNotification Windows API or from the memory calculation, SQL Server responds to the current memory situation on a specific system.
This provides the following benefits:The system does not page out the working set of the SQL Server process.The necessary database pages are available in memory to reduce physical I/O needs.For more information, see the "Dynamic memory management" topic and the "Server memory options" topic in SQL Server Books Online. ( )
Free MCITP 70-450 Practice Test Questions - Accelerated Ideas. SQL Server 2008: Performance Data Collector. With Performance Data Collector in SQL Server 2008, you can now store performance data from a number of SQL Servers in one central location.
This data is collected by a collection set on each server and stored in a shareable management data warehouse (MDW). Reports can be generated from this data using the built-in reports or generating your own with reporting Services. Brad McGehee explains more. In a perfect world,SQL Server would include a single tool that would not only automatically collect all the important performance dataDBAs need to identify and fix performance issues Traditionally, SQL Server has included a lot of tools to help DBAs identify and troubleshoot performance problems, such as Profiler, System Monitor, the Database Engine Tuning Engine, Management Studio, DMVs, T-SQL commands, and so on.
In a nutshell, the Performance Data Collector is designed to help DBAs these ways: When the Performance Data Collector is first set up, it does a lot of behind the scenes work. Working with tempdb in SQL Server 2005. Published: June 26, 2006 SQL Server Technical Article Writers: Wei Xiao, Matt Hink, Mirek, Sunil Agarwal Technical Reviewer: Wei Xiao Applies To: SQL Server 2005 Summary: This white paper describes how SQL Server 2005 uses tempdb.
On This Page. Help! My SQL Server Log File is too big!!! Overgrown transactional log files can turn into real problems if they are not handled properly. Today SQL Server consultant Tim Chapman discusses the perils of not handling SQL Server log growth properly, and what can be done to correct the problems. Over the years, I have assisted so many different clients whose transactional log file has become "too large" that I thought it would be helpful to write about it. The issue can be a system crippling problem, but can be easily avoided. Today I'll look at what causes your transaction logs to grow too large, and what you can do to curb the problem. The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005.
When you use database mirroring in Microsoft SQL Server 2005, SQL Server automatically propagates any changes on the principal database to the mirror database. However, if you run the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statements to shrink the principal database, the shrink operation is not duplicated on the mirror database. Database mirroring will change the physical file sizes only after a checkpoint. Method 1 To work around this problem, run the following statements to create a new stored procedure in the master database. Then, use this stored procedure to shrink the principal database instead of running the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement. Transaction Log Physical Architecture.
The transaction log is used to guarantee the data integrity of the database and for data recovery. The topics in this section provide the information about the physical architecture of the transaction log. Understanding the physical architecture can improve your effectiveness in managing transaction logs. The transaction log in a database maps over one or more physical files.
Conceptually, the log file is a string of log records. Physically, the sequence of log records is stored efficiently in the set of physical files that implement the transaction log. Factors That Can Delay Log Truncation. Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time. The following table briefly describes the values of the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. Log truncation cannot happen during any backup or restore operation. In SQL Server 2005 and later versions, log backups can occur during a data backup. However, log truncation cannot occur during such log backups, because all of the transaction log must remain available to the data backup operation. Setting Up Delegation for Linked Servers. How to Implement Kerberos Constrained Delegation with SQL Server 2008.
Writer: Kevin McDonnell Technical Reviewers: Greg Campbell, Jesus Dougan, Jivko Dobrev, Dan Benediktson Published: July 2009. How to troubleshoot the "Cannot generate SSPI context" error message. You may not be able to connect to an instance of SQL Server by using an anonymous login. Understanding Kerberos and NTLM authentication in SQL Server Connections - SQL Protocols.
Security Account Delegation. How to perform a SQL Server distributed query with OLAP Server. This article describes how to perform a SQL Server distributed query to retrieve data from an OLAP Services (or Analysis Services) cube. SQL Linked Server Query failed with “Login failed for user …” - SQL Protocols. Configuring Linked Servers for Delegation. Linked Server - Part 2. Introduction. Security for Linked Servers. Linked server error message on a SQL Server 2005 machine. Kerberos Error Configuring SQL Server Linked Server for Delegation - SQL Server Cast. Configuring Kerberos Authentication. Double-hop authentication: Why NTLM fails and Kerberos works - Beside the Point. Message 18456 from a distributed query. FIX: Error message when you use the SQL Server Native Client provider to run queries between two linked servers in SQL Server 2005 Service Pack 1: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'" Kerberos Error Configuring SQL Server Linked Server for Delegation - SQL Server Cast.
Benjamin Nevarez : Rebuilding Indexes vs. Updating Statistics. Reducing SQL Server Index Fragmentation. How to Detect Table Fragmentation in SQL Server 2000 and 2005. SQL Server 2005 and 2008 - Backup, Integrity Check and Index Optimization - Documentation. DBCC INDEXDEFRAG (Transact-SQL) DBCC DBREINDEX vs. DBCC INDEXDEFRAG - SQL Server Magazine – The smart guide for world-class applications - Forums - SQL Server 2000/7.0 - Administration. Understanding SQL Server's DBCC SHOWCONTIG. Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005.
Basics of Statistics in SQL Server 2005. SQL Server UPDATE STATISTICS. Red Gate Software - SQL Monitor. Statistics VS Index Rebuild - Fabiano Neves Amorim - SQL Server. Statistics Used by the Query Optimizer in Microsoft SQL Server 2005. There is insufficient system memory in resource pool 'internal' to run this query" How to troubleshoot SQL memory issues part II - THE OFFICIAL BLOG OF THE SBS "DIVA"
How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values - CSS SQL Server Engineers. How It Works: DBCC MemoryStatus Locked Pages Allocated and SinglePageAllocator Values - CSS SQL Server Engineers. Monitoring SQL Server Agent with Powershell. Pigou, The Economics of Welfare, Part I, Chapter 1. MALER%20-%20welfare1. Welfare-economics1. MALER%20-%20welfare1.
SQL SERVER ? 2008 ? Configure Database Mail ? Send Email From SQL Database ? Journey to SQL Authority with Pinal Dave.