casinos

Posts Tagged ‘ITPro’

SQL Server 2014: Checksum with backups March 14th, 2014

Vinod Kumar

At the MTC, I have been a big time supporter of using CHECKSUM’s in your Page_Verification setting as a best practices. Some of these technology innovations that we do are hidden deep somewhere in-between the options that we don’t get to see. Inorder to get the best of your SQL Server and detect IO related potential issues, it is highly recommended to turn this setting ON.

Now, we also recommend using the CHECKSUM verification with backups. Many a times have seen customers actually have their own third party solutions to take backups and many times this option is never being used. The consequence of this is, a bad backup yields a bad corrupt restore too. So I highly recommend you read this KB article for prior to SQL 2014 deployments.

What is new with SQL 2014?

From SQL Server 2014 onwards we introduce a capability of enabling checksum at the server level by default irrespective of the backup tool that you use. This is an nifty and handy setting and I will be recommending my customers to use this by default.

The image above shows the setting available by default. So to enable the same use the following command.

sp_configure ‘backup checksum default’, 1

GO

RECONFIGURE

As I sign off this new learning, wanted to check how many of you use the CHECKSUM feature as described at the database level atleast? I am getting into this small learning experiences working with SQL Server 2014 for a while and I will keep posting them in future blogs too.

Continue reading...


 

SQL Server 2014: SELECT INTO is parallel March 7th, 2014

Vinod Kumar

Recently I was performance tuning and optimization for a customer and working on SELECT INTO statements code blocks. I saw a weird behavior and a self learning hit when I saw something interesting. In SQL Server 2014, this command seems to be running in parallel. Cool and a must from performance point of view. To reproduce this simple learning, I executed the below query on my SQL 2014 CTP2 box:

Looking at the execution plan revealed the following. Look at the Parallelism and Gather Stream to confirm the same.

Sometimes, there are these nifty tiny features that get added to the product that we stumble upon by accident. Since my TempDB is in compatibility of 120 (SQL 2014), I thought of making an interesting test. I changed my compatibility level to 100 for AdventureWorks and did the test again. To my surprise I saw the Parallelism is gone !!! Woot, that was quite a learning.

Do let me know if you get the same behavior on a pre-SQL 2014 box.

Continue reading...


 

SSMS Tips–Object Explorer Details February 5th, 2014

Vinod Kumar

Today’s blog is inspired from the use of common tools that we use day-in-day-out. For me when playing with SQL Server, it will be SQL Server Management Tool (SSMS.exe). This blog has a number of posts around SSMS Tips like – SSMS: T-SQL Debugger Shortcuts, SQL Server: SSMS Tips II, SQL Server 2012 : SSMS Tips, SQL Server 2012: Snippets and IntelliSense and few more. So do look at them too.

In this blog let me just talk about a simple addendum screen that we hardly use but has tons of valuable information hidden inside SSMS – it is Object Explorer Details. For easier understanding I am just going to use a number of pictures to illustrate what I have actually done :).

Once you are in SSMS, click on F7 shortcut or use the View –> Object Explorer Details keyboard navigation as shown in picture below.

This bring the Object Explorer Details in the usual location where we normally have the Query Window. You can navigate to any node like the Object Explorer or click on a node on Object Explorer and it will populate the related details in the Details pane as shown in the next image. Below we are at the AdventureWorksDW database and have selected the Tables node. This enumerates and lists all the tables in the given database with few interesting properties.

Some additional capability in the Details pane is the ability to search for an object using the Search box on the top and once we are at the object, we can go ahead and click the double arrow (as in figure) to synchronize the Object Explorer View. It is quite an handy feature to use.

The Columns in the Object Explorer Details are customizable and quite interesting. From the header if you right click we will be presented with a list of additional columns that we can include in our view. The list changes based on the node that you are currently on. So “right-click” every header and play to your hearts desire.

In the above options, I selected Data Space Used (KB) and Row Count for my example. We can also remove any unnecessary columns which we don’t want to see too. Now with this data it becomes interesting. In the below figure I have done two things, a) sorted the “Row Count” Column and then b) selected about 10 rows from the OED pane. Now just CTRL+C and take these values to Excel :).

I have pasted the same inside Excel and it looks like below.

Inside Excel we have the freedom to do a number of things. One of the features that I have loved using the suggested charts, formatting of Excel 2013. When the table is selected, a small icon appears on the bottom right side – click on it and I have selected the Formatting –> Data Labels.

Other shortcut to play around is ALT+F1 to get a chart quickly populated. Below is a sample output that I have shown.

I am sure you have played around with Object Explorer Details before. If you haven’t played around till date then I am sure you will do now. Do let me know if you find this interesting and we will followup with many of these tips in the future.

Continue reading...


 

SQL Server AlwaysOn-Readable Secondary Setting January 3rd, 2014

Vinod Kumar

Recently I was questioned by two different customers on the very topic and I thought it was worth writing a mini-post to start the year :). The question was simple, what is the fundamental difference of values of “Yes” and “Read-Intent only” values when we are configuring our AlwaysOn Availability Groups? Aren’t they the same?

If you check the configuration of AlwaysOn we will be presented with these three options and these can be changed even later. This blog is to simply explain what these three mean.

No: This is the easiest of the lot. It just means we will not allow any connections to this server.

Yes: This is used for legacy purposes where *any TDS client* who wants to connect to a secondary replica explicitly for reporting workload can connect.

Updated as per Robert Comments: You can still connect to a instance marked as Yes via the routing list as it is an readable copy.

Read-intent-only: In this option we explicitly need to give the connection string property of “ApplicationIntent=ReadOnly”. Read more about connection strings in my previous blog – SQL Server AlwaysOn–Connection Strings. The only difference here is that you have explicitly shown the intent that the connection is going to be read only and it removes the caveat of previous point because now the routing-list / listener takes care of routing your request to the first available readable secondary as per the configuration. Hence for all practical purposes, for new applications please use this option.

Irrespective to the option selected, if your application fires a write operation to a ReadOnly server, the application will fail on the first DML or DDL operation performed in that connection.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...


 

Virtualization Journey with SQL Server–Windows Server 2012 December 17th, 2013

Vinod Kumar

This is in continuation to the previous post Virtualization Journey with SQL Server where we were trying to understand the WHY part. In this post, let me walk through some of the hardware innovations that one needs to be aware when working with Windows Server 2012 and Virtualization in specific. These are the same conversation I have with customers day-in-day-out when they come to MTC for sessions.

Windows Server 2012 provides a number of compelling capabilities to help organizations gain greater scalability and build reliable virtualized infrastructure for their mission-critical workloads like SQL Server 2012. Don’t worry, this whole integration of specific version of hardware with various versions of software is like a jigsaw puzzle and I will try to bring parts of them together for you from an easily understandable format. I don’t consider myself a virtualization expert but I am surely learning quite a lot of this concepts which I plan to share with you all.

Hardware Innovations and considerations

The hardware requirements of Windows Server 2012 Hyper-V help to ensure that it is installed correctly and makes optimum use of virtualization technology. Hyper-V requires a 64-bit processor that includes hardware-assisted virtualization and hardware-enforced Data Execution Prevention (DEP). Let me take a quick stab at what these actually are:

Hardware-assisted virtualization: This is available in processors that include a virtualization option to enable the full virtualization of host machines. The Windows Server 2012 Hyper-V role supports hardware assisted virtualization processers from the Intel VT and AMD-V processor families. Using this feature, Hyper-V puts a layer between the processors enabled with hardware-assisted virtualization and the host operating system. This facilitates interaction between guest operating systems and the underlying hardware via the host or main operating system for better performance and control over hardware resources.

Hardware-enforced Data Execution Prevention must be available and enabled. Specifically, you must enable Intel XD bit (execute disable bit) or AMD NX bit (no execute bit). More details on specifics can be read from Wikipedia. To use this feature you need to enable knobs from the BIOS and this will vary from the chip manufacturer as mentioned above.

Windows Server 2012 provides different deployment options, including Server with a GUI and Server Core Installation. The Server with a GUI option is the Windows Server 2012 equivalent of the full installation option available in Windows Server 2008 R2. The Server Core Installation option reduces the space required on disk, the potential attack surface, and especially the requirements for servicing and restarting the server.

What I recommend folks are, use the Server Core Installation option for setting up a SQL Server virtualization environment. This helps to reduce the space required on disk and the potential attack surface. It also helps to better separate management and deployment activities, reduce the overhead of updating patches, and minimize the requirements for servicing and restarting the server.

Note: I highly recommend checking the Windows Server Catalog lists for thousands of hardware and software items compatible with Windows Server 2012.

Scalability by numbers with Windows Server 2012 

Windows Server 2012 Hyper-V provides significant scalability improvements over Windows Server 2008 R2 Hyper-V. These below features help to ensure that the virtualization infrastructure is compatible with the largest scale-up servers and can support the configuration of large, high-performance virtual machines to handle workloads that might need to scale up significantly.

System  Resource  Windows Server 2008 R2 Hyper-V  Windows Server 2012 Hyper-V 
Host  Logical Processors  64 320
Physical Memory  1 TB  4 TB 
Virtual CPUs per Host  512 2048
VM  Virtual CPUs per VM  4 64
Memory per VM  64 GB  1 TB 
Active VMs per Host  384 1024
Guest NUMA  No  Yes 
Cluster  Maximum Nodes  16 64
Maximum VMs  1000 8000

Significant improvements also have been made within Windows Server 2012 Hyper-V to support increased cluster size and a higher number of active virtual machines per host. Windows Server 2012 Hyper-V supports up to 8,000 virtual machines on a 64-node failover cluster. In addition, more advanced performance features such as in-guest NUMA are supported by Windows Server 2012 Hyper-V virtual machines.

I think this is lot of information to digest in a single day and do research, the next blog on this series will be around Virtualization and understanding computation (Logical Processors, Virtual Processors, NUMA, host reserve etc). Stay tuned.

Continue reading...