Study your Data

Study your server reports do the work for you! Instant file initialization and lock pages in memory are 2 windows operating system options that can help us improve our overall SQL Server performance. These options are already checked in the Server Dashboard report available in my free tools . We will talk about both options and how to check them. Note that these options are for SQL Server running under Windows OS, for Linux systems you can ask Carlos Robles via his webpage or twitter. He has a lot of experience working with databases (SQL Server, Oracle, MySQL, Sybase) running under UNIX environments. Instant file Initialization When you create any file on the operating system, any data left in disk is overwritten by filling with zeros all the file, that could take some time depending of the file size. For SQL Server this is done when you restore a backup, perform an auto growth operation, create a data/log file (if you put an initial value of several GB this zeroing

Which cup will you prefer to go the less number of times to the coffee maker? image was taken from here Let us imagine this: Every morning when you get to the office, you must drink coffee to be fully awake, if you are a coffee lover, maybe you should have your own personalized 24 oz cup to put it. Let's say one day you lose your cup and you have to grab one of the tiny-ugly cups available in the office, now you have to put the coffee on it several times to drink the same 24 oz, this means you will have to leave your desk several times. Something similar happens with Page Life Expectancy (PLE). This is a measure that indicates us how long a data page stays in memory(in seconds) so it can be reused by other SQL Processes without having to read the same page from disk again (which translates in less I/O and less overhead). So with this in mind, the higher the value the better, but, how much is high enough for our system? Right PLE value for my SQL Server Long time ago,

Is your code "This" robust? What is Collation? In short words: are the sorting rules, case, and accent sensitivity properties for your data. (if you want to read more, click here ). If you are dedicated to database development or you provide support to a lot of user databases, then you must be aware of the collation of the database at the moment to write your queries to avoid execution failures at deploy stage. On this post, I will show you some of the most common errors related to collation and how to avoid them to have solid T-SQL queries. 1. Your DEV machine (all defaults) If you just install SQL Server in your dev machine and use all the defaults, including collation, it is very likely you won't have any issues of this kind, so let us take the following TSQL as an example: USE School; --Current database and master SELECT name, collation_name FROM sys.databases where database_id = DB_ID() or database_id =1; SELECT DEP.* FROM Department