casinos

Posts Tagged ‘Performance’

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...


 

Virtualization Journey with SQL Server December 16th, 2013

Vinod Kumar

With the proliferation of applications in everyday use, enterprises have an increasing need for more instances of databases such as SQL Server. Most applications have their own set of database requirements, leading to the use of multiple versions of databases and to significant costs related to the databases and related hardware resources. More importantly, the hardware deployed for databases is not fully utilized or there is a demand to scale up hardware resources to meet peak utilization on certain databases. Therefore, it has become important to control the cost of databases  and related hardware resources, and to optimize and scale the use of hardware resources in different scenarios to provide better flexibility and maintain service level agreements (SLAs). An ideal solution to this problem is virtualization.

Virtualization is fairly common now. Many organizations worldwide have moved beyond the nascent stage into being more advanced users of server virtualization, specifically. These organizations have gained benefits in the areas of costs, efficiency, operations, availability, agility, and resiliency.

Microsoft SQL Server 2012 and Windows Server 2012 provide a host of new features that can be used to effectively virtualize demanding database workloads that previously were not considered for virtualization. Let me take baby steps in this understanding of Virtualization and what one needs to know in this topic area – this is most likely to be multiple part post, let me start on some basics.

Note: This is an important topic because close to 50% enterprises I get to talk with have already deployed their Tier-2 application already in an virtualized environments and close to 25% or more are planning to deploy in the immediate near future too. So these discussions I have with folks are always around the best practices.

Why Virtualize?

Organizations today want the ability to consistently and coherently develop, deploy, and manage their services and applications across on-premises and cloud environments. New and enhanced capabilities in Windows Server 2012 Hyper-V can help customers reduce costs, while at the same time providing increased agility and flexibility.

Performance and Scalability: Because SQL Server can use an operating system’s maximum processor and memory capacity, Windows Server 2012 provides support for up to 640 logical processors (cores) over 64 sockets and up to 4 TB of memory (RAM).

The NIC Teaming feature in Windows Server 2012 helps improve the reliability of the networking system and ensures high availability of the network. It also improves the network throughput for SQL Server data. (These days, I recommend this feature to all my customers with this).

Windows Server 2012 features improvements in Server Message Block (SMB), including the ability to store SQL Server database files on remote file shares, it also offers multiple deployment options for database server storage. More about storage in future blogs.

Availability: Windows Server 2012 supports running SQL Server 2012 on Windows Server Core. This helps to reduce the surface area for attack and reduces the need to apply patches at the operating system level.

With the Cluster Aware Update feature of Windows Server 2012, maintenance of SQL Server cluster nodes can be automated, reducing downtime and improving reliability. With dynamic quorum management and the SQL Server AlwaysOn features, SQL Server clusters can achieve extremely high availability.

By combining Windows Server 2012 with System Center 2012, administrators can comprehensively manage demanding applications (such as SQL Server 2012 workloads) as well as the infrastructure – including physical and virtual resources—in an integrated and unified manner. Some of the benefits I talk with customers are:

  1. Higher capacity vCPUs (up to 64), Memory (up to 1  TB), and virtual machine density (up to 8,000 per cluster).
  2. Hyper-V support on Non-Uniform Memory Access (NUMA) and Fibre Channel.
  3. Faster and simultaneous live migration and dynamic quorum support in SQL
    Server AlwaysOn cluster.
  4. Same management tool (System Center) for SQL Server virtual machines in both private and public clouds.

Now that I have put the fundamental building blocks or reasons to virtualize, I will blog in future posts around:

  1. Hardware innovations
  2. Compute considerations
  3. Storage considerations
  4. Networking considerations
  5. SQL Configurations etc

As this is an important topic, let me know if you want me to write on specific topic around Virtualization and SQL Server. And also let me also know if your organizations are running SQL Server inside a virtualized environments? Are there problems that you wished were explained anywhere? Let me know and I will cover them in future posts.

Continue reading...


 

SQL Server AlwaysOn–Adding node fails December 11th, 2013

Vinod Kumar

Last week I got a call from our internal deployment teams that when they were adding a node to Windows Cluster for SQL Server AlwaysOn it was timing out and it was some sort of latency issues. It was interesting because the latencies between the two datacenters which were far apart (Delhi and Hyderabad) was around 28ms. The best way for me to understand what was going on was to understand what is happening behind the scenes.

First, I asked what their current setup was. They explained they were planning to achieve the architecture as shown below. They were able to setup on the Primary DC1 the two servers SRV1 and SRV2. Now on the Windows Cluster they were trying to add the third node (SRV3) and it was throwing an error. The same was the case when they tried to add SRV4 node.

The usual ping, tracert etc yielded no big information for us. Infact there was no problem in connectivity between the sites when this operation was performed. At this point in time, it got clear it is not an SQL Server AlwaysOn problem but more of a Windows Cluster Problem that we had in our hands.

The next step was to actually get the Cluster Error Logs and most importantly we wanted to see the actual error message.

Failed to access remote registry on ‘OLTPDB.POC.MYDOMAIN.IN’. Ensure that the remote registry service is running, and have remote administration enabled.

The error message was interesting because it didn’t mention anything about latencies and the deployment partner was actually troubleshooting in the wrong direction.

Actual Solution

It is interesting to find what could have gone wrong.

  1. Firewall is blocking Remote Registry connection between SRV1 and SRV3/SRV4
  2. Though Cluster port 3343 is open, Remote Registry is needed.
  3. Remote Registry works on RPC which uses Dynamic Ports + Port 135

Once we figured out this problem, we had to involve the Network Team to open Firewall for Dynamic Ports or Open for all communications for the specifically Cluster nodes on both sides.

If you are interested in knowing about Remote Registry and how to query the ports, use the Port Query Tool.

It is worth to mention the ports assignment for RPC:

 Service Name

UDP

TCP

HTTP

80, 443, 593

80, 443, 593

Named Pipes

445

445

RPC Endpoint Mapper

135

135

RPC Server Programs

<Dynamically assigned>

<Dynamically assigned>

On customer side the port analysis revealed something interesting. We had to open the communication port of 445 in this specific case.

Port

Protocol

Mode

Status

1433

TCP

Two way

Listening

5022

TCP

Two way

Listening

3343

TCP/UDP

Two way

Listening

135

TCP

Two way

Listening

137

UDP

Two way

Listening

445

UDP/TCP

Two way

Not Listening

The issue was closed as soon as the team worked with the networking team. This was an important learning for us in multiple ways:

  1. SQL Server AlwaysOn is just not about SQL Server, we need to have our Windows Team also involved.
  2. Sometimes a Windows Error message can lead us to Network and Infrastructure requirements that we normally oversee off.
  3. It is a team work of ALL parties (SQL Administrator, Windows Administrator and Network Administrator).

Finally, wanted to mention – Learning is a journey and I seem to be learning something every single day.

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...