Top 10 list of SQL Server counters in Perfmon

Top 10 list of SQL Server counters in no particular order. For each counter I have described what it is, and in some cases I have described the ideal value of these counters. This list should give you a starting point for developing the metrics you want to use to measure database performance in your SQL Server environment.

1. SQLServer: Buffer Manager: Buffer cache hit ratio

The buffer cache hit ratio counter represents how often SQL Server is able to find data pages in its buffer cache when a query needs a data page. The higher this number the better, because it means SQL Server was able to get data for queries out of memory instead of reading from disk. You want this number to be as close to 100 as possible. Having this counter at 100 means that 100% of the time SQL Server has found the needed data pages in memory. A low buffer cache hit ratio could indicate a memory problem.

2. SQLServer: Buffer Manager: Page life expectancy

The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. You should watch this counter over time to determine a baseline for what is normal in your database environment. Some say anything below 300 (or 5 minutes) means you might need additional memory.

3. SQLServer: SQL Statistics: Batch Requests/Sec

Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by your SQL Server box. The higher the number, the more queries are being executed on your box. Like many counters, there is no single number that can be used universally to indicate your machine is too busy. Today’s machines are getting more and more powerful all the time and therefore can process more batch requests per second. You should review this counter over time to determine a baseline number for your environment.

4. SQLServer: SQL Statistics: SQL Compilations/Sec

The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. To do that, divide the number of batch requests by the number of compiles per second to give you a ratio of the number of batches executed per compile. Ideally you want to have one compile per every 10 batch requests.

5. SQLServer: SQL Statistics: SQL Re-Compilations/Sec

When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of time a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations so you want to minimize the number of re-compiles. Ideally you want to keep this counter less than 10% of the number of Compilations/Sec.

6. SQLServer: General Statistics: User Connections

The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. You need to watch this counter over time to understand your baseline user connection numbers. Once you have some idea of your high and low water marks during normal usage of your system, you can then look for times when this counter exceeds the high and low marks. If the value of this counter goes down and the load on the system is the same, then you might have a bottleneck that is not allowing your server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using your SQL Server instance.

7. SQLServer: Locks: Lock Waits / Sec: _Total

In order for SQL Server to manage concurrent users on the system, SQL Server needs to lock resources from time to time. The lock waits per second counter tracks the number of times per second that SQL Server is not able to retain a lock right away for a resource. Ideally you don't want any request to wait for a lock. Therefore you want to keep this counter at zero, or close to zero at all times.

8. SQLServer: Access Methods: Page Splits / Sec

This counter measures the number of times SQL Server had to split a page when updating or inserting data per second. Page splits are expensive, and cause your table to perform more poorly due to fragmentation. Therefore, the fewer page splits you have the better your system will perform. Ideally this counter should be less than 20% of the batch requests per second.

9. SQLServer: General Statistic: Processes Block

The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up. Ideally you don't want to see any blocked processes. When processes are being blocked you should investigate.

10. SQLServer: Buffer Manager: Checkpoint Pages / Sec

The checkpoint pages per second counter measures the number of pages written to disk by a checkpoint operation. You should watch this counter over time to establish a baseline for your systems. Once a baseline value has been established you can watch this value to see if it is climbing. If this counter is climbing, it might mean you are running into memory pressures that are causing dirty pages to be flushed to disk more frequently than normal.

What is SQL Server Profiler?

The SQL Server Profiler, one of SQLServer’s most powerful auditing and analysis tools. The SQLServer Profiler gives you a basic understanding of database access and helps you answer questions such as these:

• Which queries are causing table scans on my invoice history table?.
• Am I experiencing deadlocks, and, if so, why?.
• What SQL queries is each application submitting?.
• Which were the 10 worst-performing queries last week?.
• If I implement this alternative indexing scheme, how will it affect my batch operations?

SQL Server Profiler records activity that occurs on a SQL Server instance. The tool has a great deal of flexibility and can be customized for your needs. You can direct SQL Server Profiler to record output to a window, file, or table.You can specify which events to trace, the information to include in the trace, how you want that information grouped, and what filters you want to apply.

SQL :Detecting and Ending Deadlocks

A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

• Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).
• Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).
• Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

HOW to Enable / Disable Users (not Logins) in SQL Server 2008

A DBA friend of mine came across a strange problem the other day. He discovered that one of the users in a SQL server database was disabled, thats a database user not a server login. At least we thought it was disabled at first, it behaved as if it was disabled and it had the small red down arrow icon that disabled server logins have, but as we all know there is no way to enable or disable database users in the Management Studio GUI.

I had a quick Google and was quite surprised to discover that lots of people had the same ‘issue’ but most peoples workaround was to delete the user and re-create it.
After some more research, it turns out that the user didnt have CONNECT permissions to the database (which I guess amounts to the same as being disabled).

You can grant CONNECT permissions using the code:

USE YourDatabase GRANT CONNECT TO User1

Or to remove the permissions:

USE YourDatabase REVOKE CONNECT FROM User1

You can check which users in a database have the CONNECT permission by executing this T-SQL:

USE DBName go SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'User1'

How to Find the Size for all Tables in SQL Databases

The below sql query will get the list of table sizes in SQL DATABASE.

SELECT
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8  AS TotalSpaceKB,
    SUM(a.used_pages) * 8  AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

@Hybrid Cloud Model

The Hybrid cloud is the mixture of public and private cloud. Non-critical activities are performed by public cloud while critical activities are performed by private cloud.

Advantages of Hybrid Cloud Model

1) Scalable

It provides both the features of public and private cloud scalability.

---

2) Flexible and secure

It provides secure resources because of private cloud and scalable resources because of public cloud.

---

3) Cost effective

It is having less cost as compared to private cloud.

---

Disadvantages of Hybrid Cloud Model

1) Networking issues

Networking becomes complex because of private and public cloud.

---

2) Security Compliance

It is necessary to ensure that cloud services are compliant with the security policies of an organization.

@Private Cloud Model

The Private cloud allows the accessibility of systems and services within the organization. Private cloud is operated only within a particular organization. But it will be managed internally or by third party.

Advantages of Private Cloud Model

1) High security and privacy

Private cloud resources are shared from distinct pool of resources and hence highly secured.

2) More Control

Private clouds have more control on its resources and hardware than public cloud because it is accessed only within the boundary of an organization.

Disadvantages of Private Cloud Model

1) Restriction

Private cloud is only accessible locally and it is very difficult to deploy globally.

2) More Cost

cloud is having more cost than public clouds.

3) Inflexible price

In order to fulfill demands, purchasing new hardware is very costly.

4) Less Scalability

Private clouds are scaled only within capacity of internal hosted resources.

How to find all users in an Active Directory group from the command line

we can get the list of users in AD group using following command line .

net group /domain TheGroupName

Public Cloud Model

Public cloud allows the accessibility of systems and services easily to general public. Eg: Amazon, IBM, Microsoft, Google, Rackspace etc.

---

Advantages of Public Cloud Model

1) Low Cost

Public cloud is having low cost as compared to private or hybrid cloud, because it shares same resources with large number of consumer.

---

2) Reliable

Public cloud provides large number of resources from different locations, if any of the resource fail, public cloud can employ another one.

---

3) Flexible

It is very easy to integrate public cloud with private cloud and hence it gives flexible approach to consumers.

---

4) Location Independent

It ensures the independency of location, because public cloud services are delivered through Internet.

---

5) High Scalability

Cloud resources are available as per the demand from the pool of resources that means they can be scaled up or down according to the requirement.

---

Disadvantages of Public Cloud Model

1) Low security

In public cloud model, data is present off-site and resources are shared publicly. Hence it does not ensure the high level security.

2) Less customizable

It is less customizable than private cloud.

How does cloud computing work

Assume that you are an executive at a very big corporation. Your particular responsibilities include to make sure that all of your employees have the right hardware and software they need to do their jobs. To buy computers for everyone is not enough.

You also have to purchase software as well as software licenses and then provide these softwares to your employees as they require. Whenever you hire a new employee, you need to buy more software or make sure your current software license allows another user. It is so stressful that you have to spend lots of money.

But, there may be an alternative for executives like you. So, instead of installing a suite of software for each computer, you just need to load one application.

That application will allow the employees to log-in into a Web-based service which hosts all the programs for the user that is required for his/her job. Remote servers owned by another company and that will run everything from e-mail to word processing to complex data analysis programs. It is called cloud computing, and it could change the entire computer industry.

In a cloud computing system, there is a significant workload shift. Local computers have no longer to do all the heavy lifting when it comes to run applications. But cloud computing can handle that much heavy load easily and automatically. Hardware and software demands on the user's side decrease. The only thing the user's computer requires to be able to run is the cloud computing interface software of the system, which can be as simple as a Web browser and the cloud's network takes care of the rest.

History of Cloud Computing

Before emerging the cloud computing, there was Client/Server computing which is basically a centralized storage in which all the software applications, all the data and all the controls are resided on the server side.

If a single user wants to access specific data or run a program, he/she need to connect to the server and then gain appropriate access, and then he/she can do his/her business.

Then after, distributed computing came into picture, where all the computers are networked together and share their resources when needed.

On the basis of above computing, there was emerged of cloud computing concepts that later implemented.

At around in 1961, John MacCharty suggested in a speech at MIT that computing can be sold like a utility, just like a water or electricity. It was a brilliant idea, but like all brilliant ideas, it was ahead if its time, as for the next few decades, despite interest in the model, the technology simply was not ready for it.

But of course time has passed and the technology caught that idea and after few years we mentioned that:

In 1999, Salesforce.com started delivering of applications to users using a simple website. The applications were delivered to enterprises over the Internet, and this way the dream of computing sold as utility were true.

In 2002, Amazon started Amazon Web Services, providing services like storage, computation and even human intelligence. However, only starting with the launch of the Elastic Compute Cloud in 2006 a truly commercial service open to everybody existed.

In 2009, Google Apps also started to provide cloud computing enterprise applications.

Of course, all the big players are present in the cloud computing evolution, some were earlier, some were later. In 2009, Microsoft launched Windows Azure, and companies like Oracle and HP have all joined the game. This proves that today, cloud computing has become mainstream.

Disadvantages of Cloud Computing

There are various disadvantages of cloud computing technology. The important disadvantages of cloud computing are given below.

1) Require a constant Internet Connection

Cloud computing is impossible without Internet connection. To access any applications and documents you need a constant Internet connection.

---

2) Require High Speed Internet connection

Similarly, a low-speed Internet connection makes cloud computing painful at best and often impossible. Web based apps often require a lot of bandwidth to download, as need to download large documents.

---

3) Stored Data Might Not Be Secure

With cloud computing, all your data is stored in the cloud. That's all well and good, but how secure is the cloud? Can't unauthorized users gain access to your confidential data?

Advantages of Cloud Computing

There are various advantages of cloud computing technology. The important advantages of cloud computing are given below.

1) Lower cost computer for users

In cloud, you don't require a high-powered (and accordingly high-priced) computer to run cloud computing's web based applications because applications run on cloud not on desktop PC or laptop.

---

2) Lower IT infrastructure cost

By using cloud computing, you need not to invest in larger numbers of more powerful servers, you also need not to require the IT staff for handling such powerful servers.

---

3) Fewer maintenance cost

The maintenance cost in cloud computing greatly reduces both hardware and software maintenance for organizations of all sizes.

---

4) Lower Software Cost

It reduces the software cost because you don't need to purchase separate software packages for each computer in the organization.

---

5) Instant software updates

Another software-related advantage in cloud computing is that users don't need to face with the choice between obsolete software and high upgrade costs. If the app is web-based, updates happen automatically and are available next time when the user logs in to the cloud.

---

6) Increased computing Power

The execution capacity of cloud servers are very high. It processes the application very fast.

---

7) Unlimited storage capacity

Cloud offers you a huge amount of storage capacity like 2000 GB or more than that if required.

@What is Cloud computing?

Cloud computing means on demand delivery of IT resources via the internet with pay-as-you-go pricing. It provides a solution of IT infrastructure in low cost.

Why Cloud Computing?

Actually, Small as well as some large IT companies follows the traditional methods to provide the IT infrastructure. That means for any IT company, we need a Server Room that is the basic need of IT companies.



In that server room, there should be a database server, mail server, networking, firewalls, routers, modem, switches, QPS (Query Per Second means how much queries or load will be handled by the server) , configurable system, high net speed and the maintenance engineers.
To establish such IT infrastructure, we need to spend lots of money. To overcome all these problems and to reduce the IT infrastructure cost, Cloud Computing comes into existence.

Characteristics of Cloud Computing

The characteristics of cloud computing are given below:
1) Agility
The cloud works in the distributed computing environment. It shares resources among users and works very fast.

2) High availability and reliability

Availability of servers is high and more reliable, because chances of infrastructure failure are minimal.

3) High Scalability

Means "on-demand" provisioning of resources on a large scale, without having engineers for peak loads.

4) Multi-Sharing

With the help of cloud computing, multiple users and applications can work more efficiently with cost reductions by sharing common infrastructure.

5) Device and Location Independence

Cloud computing enables the users to access systems using a web browser regardless of their location or what device they use e.g. PC, mobile phone etc. As infrastructure is off-site (typically provided by a third-party) and accessed via the Internet, users can connect from anywhere.

6) Maintenance

Maintenance of cloud computing applications is easier, since they do not need to be installed on each user's computer and can be accessed from different places. So, it reduces the cost also.
7) Low Cost
By using cloud computing, the cost will be reduced because to take the services of cloud computing, IT company need not to set its own infrastructure and pay-as-per usage of resources.
8) Services in pay-per-use mode
Application Programming Interfaces (APIs) are provided to the users so that they can access services on the cloud by using these APIs and pay the charges as per the usage of services.

How to Install SQL Server 2008 R2 Cluster installation step by step process

SQL 2008 R2 Cluster Installation Steps:

Today we will discussed about SQL 2008 R2 cluster installation step by step.

1. Check the prerequisite of failover cluster .
2. Validate and ensure that failover cluster is working fine .
3. Get the SQL instance Name, cluster Network Name and IP address from users or Client.
4. Ensure that Storge (SAN)added to failover cluster resources  .
5. Once windows failover cluster good , start the sql installation .


How to install SQL 2008 R2 cluster instance on 2 node cluster servers.

1 . Copy the SQL media into both nodes .
2.  Go to Setup.exe and run the setup.exe