Fixing Page Life Expectancy (PLE) What is Page Life Expectancy (PLE), what makes it drop, and how can I manage memory better?
Abusing disks slows many database servers, and it’s often something you can fix with tuning and not spending extra money on better disks or more memory. It takes a very long post to get through all of that, but if you stick with me through this then you’ll be looking at your servers from new angles with an effort that will be noticed by the Sys Admins, SAN Admin, the users, and your boss.
Before we being, there are some ground rules we need to get out of the way defining PLE and understanding there are external memory factors. There’s no shame in skipping that and going straight to the focus of this post. Personally, I find the next two sections a little dry, but I’m also my harshest critic! Define PLE Before we get too deep into it, lets make sure we’re on the same page on a couple things.
Page Life Expectancy is the number of seconds the average page of data has been in the buffer pool. Results. Clustered Columnstore Indexes – part 1 (“Intro”) After the Microsoft’s first attempt to improve DataWarehouse performance drastically with introduction of the Nonclustered Columnstore Indexes, which are extremely limited (non-updatable, schema modification prohibited, limited number of data types supported, cannot be unique, batch execution mode is also very limited in SQL Server 2012, etc), this time for SQL Server 2014 we shall have a feature that really promises to change this game around – CLUSTERED columnstore indexes.
As far as I know, not many organizations around the world are using Nonclusted NonUpdateable Columnstore Indexes mainly because of it is being a very new feature and the other reasons is its limitations. Since CTP1 is already available I decide to give it a try and to play with Clustered Columnstore Indexes a bit After the process of encoding and compression is done, then the segments and the dictionaries are converted to Blobs and stored inside of the SQL Server.
Some words about some of the basic operations: SQL Server Performance Dashboard Reports in SSMS – Introduction, Install, DateDiff error & Modified for SQL 2008 - Microsoft SQL Server Tips & Tricks. SQL Server Performance Dashboard Reports in SSMS – Introduction, Install, DateDiff error & Modified for SQL 2008 Many of you seasoned DBA’s out there would have heard about the SQL Server 2005 Performance Dashboard Reports and used it before.
I find the reports highly useful, time saving and just like a car dashboard, allows me to get a picture of how my SQL Server is performing. For those who don’t know about it, you can download it from here and add it as a custom report to Management Studio (SSMS). It is a free download! Read on below to find out how to install it and use it on your SQL Server.
IntroductionWell, the reports are mainly intended to quickly identify performance problems and help resolve them. 1) CPU bottlenecks (which queries are consuming the most CPU and Plan for this query) 2) IO bottlenecks (which queries are performing the most IO and Plan for this query). 4) Blocking. SQL Server Performance Blog – Expert advice on SQL server performance. Some Suggested SQL Server 2008 R2 Instance Configuration Settings. Depending on your anticipated workload and which SQL Server components you have installed, there are a number of instance level settings that you should consider changing from their default values.
These include Max Server Memory, optimize for ad-hoc workloads, default backup compression, max degree of parallelism, and the number of TempDB data files for the SQL Server instance. Max Server Memory Max Server Memory controls how much memory can be used by the SQL Server Buffer Pool, which is where cached data is held in memory after it is read in off of the disk subsystem. This is only for use by the relational Database Engine. It has no effect on other SQL Server components, such as SSIS, SSRS, or SSAS. The idea here is to limit how much memory the buffer pool can use in order to avoid starving other SQL Server components or the operating system for memory.
Monitoring running processes in SQL Server 2008 R2. You can view the current connections and processes that running on a sql server instance in two ways Using Activity Monitor Using T-SQL statements To open the Activity Monitor in SQL Server Management Studio Right click the server then select the Activity Monitor option as shown below Activity Monitor gives you an option to view who is connecting to your machine and what they are doing? It shows 4 graphs Processor Time, Waiting Tasks, database I/O and Batch Requests The columns in the Activity Monitor are very useful to debugging you to understand the You can refresh the data on graph by right clicking on it and selecting refresh interval. To demonstrate to view the lock processes on your SQL Server Write the following query in sql server management studio in new query window as follows 2: DELETE FROM [DimProduct] 3: WHERE ProductKey = 10 because we have not written Commit TRAN command nothing will saved to the database. 1: SELECT * FROM dbo.DimProduct.
SQL Server Magazine – The smart guide for world-class applications.