SQL Server Security

 Let’s get some basic definitions down first.

Most SQL Server databases have a number of users viewing and accessing data, which makes security a major concern for the administrator. The smart administrator will take full advantage of SQL Server security roles, which grant and deny permissions to groups of users, greatly reducing the security workload. 

The first step in protecting your client’s data is determining which users need to view which data and then allowing access to only those users. For example, a payroll clerk probably views salary figures for everyone in your company while team managers have access to salaries for team members. Individual employees have no need to view salaries at all. 

You must also decide which users can change the data. For example, although someone in fulfilment might need to verify a customer’s address, you may want only an account executive or a specially trained data entry clerk to change that account’s address. 

Accommodating a number of users could be a huge task if it weren't for the Windows security model, which can easily accommodate many users with one role. A role defines what a user can and can’t do within a database, and multiple users can share the same role. I’ll discuss roles and their relationship to Windows groups and show you how a role can grant or deny access to multiple users at the same time. 

The benefits of using roles 

Roles are a part of the tiered security model: 

·         Login security—Connecting to the server

·         Database security—Getting access to the database

·         Database objects—Getting access to individual database objects and data

First, the user must log in to the server by entering a password. Once connected to the server, access to the stored databases is determined by user accounts. After gaining access to an actual database, the user is restricted to the data he or she can view and modify.

The main benefit of roles is efficient management. Imagine a group of 1,000 users suddenly needing to view or modify new data. Using Windows security, you simply select an existing Windows group and assign it to a SQL Server role—instead of modifying 1,000 user accounts. To clarify, Windows groups consist of users with access to the Windows network, but SQL Server roles belong strictly to SQL Server. You’re simply granting permissions to SQL Server data and objects to valid Windows users.

Role types

Server roles are maintained by the database administrator (DBA) and apply to the entire server, not an individual database file. The public role sets the basic default permissions for all users. Every user that’s added to SQL Server is automatically assigned to the public role—you don’t need to do anything. Database roles are applied to an individual database.

We have mainly two types of roles

1.Server roles

2.Database roles

1.Server roles

Fixed roles

The fixed server roles are applied serverwide, and there are several predefined server roles:   

• SysAdmin: Any member can perform any action on the server.
• ServerAdmin: Any member can set configuration options on the server.
• SetupAdmin: Any member can manage linked servers and SQL Server startup options                                  and  tasks.
• Security Admin: Any member can manage server security.
• ProcessAdmin: Any member can kill processes running on SQL Server.
• DbCreator: Any member can create, alter, drop, and restore databases.
• DiskAdmin: Any member can manage SQL Server disk files
• BulkAdmin: Any member can run the bulk insert command

2.Database Level Security

Database roles (also referred to as database-level roles) are security roles that exist on a database level, as opposed to the server level. If you are familiar with any aspect of system administration, database roles are similar to groups in the world of Windows system administration. Just like a Windows group, when a user is added to a role they inherit all the rights and permissions of the role.

Fixed roles

Fixed roles automatically exist in each database. Adding a user to one of these roles will not change that user’s permissions in any other database.

Any user or role can be added to a database role. Once a user has been added to a role, they can add other users or roles to that role.

A Word of Warning: Be exceptionally careful when adding flexible roles to a fixed role. You could very easily elevate privileges for a large number of users in one simple step.

With that warning out of the way, let’s take a look at the fixed roles available in SQL Server:

• db_owner
• db_securityadmin
• db_accessadmin       
• db_backupoperator
• db_ddladmin
• db_datawriter
•  db_datareader
•  db_denydatawriter
•  db_denydatareader
•  Public

Db_owner

Users in the db_owner role have it all, within a single database. They can grant and revoke access, create tables, stored procedures, views, run backups and schedule jobs. A user who is db_owner can even drop the database.

However, just because you have the keys to the kingdom doesn’t mean that you can do everything. Users who have been granted db_owner will still need specific permissions to run traces and view many of the dynamic management views. Why is that? Those are managed at the server level and will require that server-level permissions or roles are granted to your login.

Why Use Db_owner?

You would want to add a user to the db_owner role if you have a user who needs to make extensive modifications to all aspects of a database:

• Creating users
• Adding them to roles
• Creating tables/views and stored procedures,
• Adding security settings for tables, views, and stored procedures

One example shown below would be a developer who is creating a database for a new application (or creating extensive modifications to an existing application), but you don’t want to add that user to the sysadmin group:

-- Create our sample database and switch to it

CREATE DATABASE TestFixedRoles;

GO

USE TestFixedRoles;

GO

-- Set up our test users

CREATE USER user_dbo WITHOUT LOGIN;

CREATE USER user_security WITHOUT LOGIN;

CREATE USER user_reader WITHOUT LOGIN;

CREATE USER user_writer WITHOUT LOGIN;

CREATE USER user_backup WITHOUT LOGIN;

GO

-- Grant db_owner to user_dbo

EXEC SP_ADDROLEMEMBER N'db_owner', N'user_dbo';

EXEC SP_ADDROLEMEMBER N'db_securityadmin', N'user_security';

-- Create an orders table as dbo

CREATE TABLE orders ( id            INT       NOT NULL   IDENTITY(1,1),

                                               orderdate     DATETIME  NOT NULL,

                                               employee_id   INT       NOT NULL,

                                               customer_id   INT       NOT NULL,

                                               quantity      INT       NOT NULL,

                                                CONSTRAINT PK_Orders PRIMARY KEY (id)

                                               );

                                             GO

-- temporarily switch to the context of user_dbo

EXECUTE AS USER = 'user_dbo';

SELECT USER_NAME(); -- This should return user_dbo

-- This will return 1 row for our orders table

SELECT t.[name] 

 FROM sys.tables AS t

WHERE t.[name] NOT LIKE 'sys%'

DROP TABLE orders;

-- revert back to our regular user

REVERT;

GO

db_securityadmin 

Users in the db_securityadmin role can modify role permissions and manage permissions. Users in this role have, in theory, almost as much power members of db_owner. The only thing that a member of db_securityadmin can’t do is add users to the db_owner role. Members of db_securityadmin also cannot add users to fixed database roles (this requires membership in the db_owner role). 

Why Use db_securityadmin? 

You might want to use db_securityadmin when you need to grant privileges to a trusted user and allow them to manage privileges across an application. Take care when granting db_securityadmin to make sure that you trust the user to not give themselves additional permissions. This risk can be alleviated by adding auditing to the database log when privileges are granted or revoked: 

EXECUTE AS USER = 'user_security';

-- this will generate three errors since

-- user_security isn't a member of db_owner.

EXEC SP_ADDROLEMEMBER N'db_datareader', N'user_reader';

EXEC SP_ADDROLEMEMBER N'db_datawriter', N'user_writer';

EXEC SP_ADDROLEMEMBER N'db_backupoperator', N'user_backup';

REVERT;

GO

-- this will now succeed since you are a member of db_owner, after all

EXEC SP_ADDROLEMEMBER N'db_datareader', N'user_reader';

EXEC SP_ADDROLEMEMBER N'db_datawriter', N'user_writer';

EXEC SP_ADDROLEMEMBER N'db_backupoperator', N'user_backup';

db_accessadmin

 Members of the db_accessadmin role have the ability to change database access. They can grant and revoke access to Windows logins, Windows groups and SQL Server logins. The users that they grant access to will be members of the Public role and will have all the privileges associated with that role. 

Why Use db_accessadmin? 

This role would be used when you have a user who is responsible for maintaining access to a specific database. When combined with db_securityadmin you have a user who is capable of granting and revoking general access to a database as well as controlling the security permissions for almost any user. This combination is quite powerful and should be granted carefully. With auditing in place, you can mitigate any risk of granting both roles to a single user.

 db_backupoperator 

Members of this role can create database backups. It’s important to note that they cannot, by default, restore the backups that they create. The only users that can restore a backup are members of the sysadmin and dbcreator server roles and the owner of the database (dbo). 

Why Use db_backupoperator? 

If you have an automated process that connects to the database and creates a backup, it would be a good idea to have all backup operations connect to the database using a user/login that only has db_backupoperator access to prevent any unauthorized data access due to a user being compromised. 

db_datareader/db_denydatareader 

Members of the db_datareader role are able to read all data from all user tables. Even the super secret table UserPayHistoryAndSocialSecurityNumbers can be read by members of db_datareader. 

Conversely, members of db_denydatareader are explicitly denied the ability to write to any user created tables. They live in the dark about the contents of the database.

 Why Use db_datareader?

 Let’s say the Accounting department has a separate database. Everyone in Accounting is able to create and run ad hoc reports directly against this database, but they shouldn’t be able to do anything else apart from seeing the contents of the database. Clearly granting db_owner access is out of the question. Your junior DBA is out sick today, so you can’t make him grant SELECT permissions to every table in the database while you go out for lunch. Instead, you can simply grant db_datareader access to the Accounting department’s Windows group. 

Why Use db_denydatareader? 

In addition to the Accounting department’s requirement to be able to create ad hoc reports as needed, HR needs to be able to run the canned reports available to them through your carefully crafted stored procedures, but they should not be able to run ad hoc reports. However, it turns out that they are also able to create and run ad hoc reports. By granting db_denydatareader permissions to the HR Windows group, you can prevent members of the HR group from running the ad hoc reports (assuming Windows Forms authentication is being used). You simply get in touch with the application developers and have them hide ad hoc reports from the HR group in the application front end. 

db_datawriter/db_denydatawriter 

Much like db_datareader and db_denydatareader, the name of this role is largely self-explanatory: members of the db_datawriter role can INSERT, UPDATE, and DELETE data from any user created table. Likewise, members of the db_denydatawriter role are explicitly denied the ability to perform INSERT, UPDATE, and DELETE operations on any user created tables. 

Why Use db_datawriter? 

db_datawriter would be a good choice for a user or login that runs an automated ETL process on a regular basis. This ensures that access to all tables is maintained even when new tables are added and reduces maintenance overhead. 

Why use db_denydatawriter? 

If you want to limit write access for a login or user, it is easy to add them to the db_denydatawriter role and allow specific access to a subset of tables. This might be desirable when users may need to make adjustments to certain tables - such as a bill, order or account balance - but they do not need the ability to modify all data in the database. 

db_ddladmin

 Members of the db_ddladmin role are able to execute DDL commands (CREATE, ALTER, DROP) within the current database. It’s pretty self-explanatory - a member of db_ddladmin can run any CREATE, ALTER or DROP command within the current database. They cannot create new databases, nor can they alter or drop the current database. 

Why Use db_ddladmin? 

Your company has brought on several consultants to help develop a new application. Their work will require that they are able to create, or change, new tables, views, indexes and stored procedures. However, they don’t need the ability to create users or manage security. In this case, your best solution would be to add the consultants to the db_ddladmin role. 

Public 

The Public role is a bit different from all of the other roles. Every database user is a member of the Public role. If a user does not have any explicit permissions on a database object, they will inherit the permissions of the Public role. It is important to note that users cannot be removed from the Public role. 

Why Use the Public Role? 

Going back to our example of the Accounting and HR departments, let’s say that there are now two databases: Accounting and HR. For the purposes of this example, only Accounting personnel should have access to the Accounting database and only HR personnel should have access to the HR database. What’s the best way to accomplish this?

The first step is to create a user for each department in their respective database. This user needs to be mapped to the appropriate Windows group. Once you have created the user and mapped it to the appropriate group, you can then add the user to the Public role.

Using this method it’s easy to add additional users and groups to the Public role without having to manage separate security settings for each one individually:

/*****************************************************************************

* PUBLIC ROLE DEMONSTRATION

****************************************************************************/

CREATE DATABASE Accounting;

GO

USE Accounting;

GO

-- This is going to fail unless you have a LOGIN called group_Accounting

CREATE USER user_Accounting FOR LOGIN group_Accounting;

GO

EXEC SP_ADDROLEMEMBER N'Public', N'user_Accounting';

GO

CREATE DATABASE HumanResources;

GO

USE HumanResources;

GO

-- This is going to fail unless you have a LOGIN called group_HumanResources

CREATE USER user_HumanResources FOR LOGIN group_HumanResources;

GO

EXEC SP_ADDROLEMEMBER N'Public', N'user_HumanResources';

GO

To begin, start with an empty database:

-- Create a new user and grant them db_securityadmin

CREATE USER user_security WITHOUT LOGIN;

CREATE USER user_test WITHOUT LOGIN;

GO

EXEC sp_addrolemember N'db_securityadmin', N'user_security';

GO

This creates a new database, a new user, and granted db_securityadmin to a new user. Remember that db_securityadmin can’t grant access to fixed database roles. However, db_securityadmin has no such limitation when dealing with flexible database roles.

Next, set up a test user for the purposes of this introduction to flexible database roles:

CREATE USER test_user WITHOUT LOGIN;

GO

This user is now a member of the PUBLIC role. What kind of access does public have?

CREATE SCHEMA Test;

GO

CREATE TABLE Test.t1 (number INT);

GO

DECLARE @i INT;

SET @i = 0;

WHILE @i < 1000

BEGIN

 INSERT INTO Test.t1 VALUES (@i);

 SET @i = @i + 1;

END

EXECUTE AS USER='Test_user';

GO

-- This will fail because we have no access to the test schema

SELECT * FROM t1;

GO

REVERT

GO

None. PUBLIC has no access, thus Test_user has no access.

Next get test_user set up with some access:

CREATE ROLE Test_role;

GO

-- The scope qualifier '::' is required.

GRANT SELECT ON SCHEMA :: test TO test_role;

GO

EXEC sp_addrolemember N'Test_role', N'Test_user';

GO

EXECUTE AS USER='Test_user';

GO

-- Success!

SELECT * FROM t1;

GO

REVERT

GO

There you have it. You can now select from the Test schema. How does this help, though, in the real world? 

For starters, by creating roles and adding users to roles you can streamline managing security through the use of roles rather than having to monitor the permissions assigned to every user, login, Windows user and Windows group. 

Second, by combining roles for managing security with schemas and stored procedures, it’s possible to carefully control and define granular access to stored procedures and data. 

Next, take a look at AdventureWorks and create an example of how you might want to accomplish this. 

This procedure simply retrieves customers who placed an order between two dates.

USE AdventureWorks;

GO

CREATE PROCEDURE Sales.GetCustomersWithOrdersBetweenDates (

 @StartDate DATETIME,

 @EndDate DATETIME 

)

WITH EXECUTE AS OWNER AS

SET NOCOUNT ON;

SELECT pc.LastName,  pc.FirstName, pc.EmailAddress

 FROM Sales.SalesOrderHeader AS ssoh

      INNER JOIN Sales.SalesOrderDetail AS ssod

                              ON ssoh.SalesOrderID = ssod.SalesOrderID

      INNER JOIN Sales.Customer AS sc

                              ON ssoh.CustomerID = sc.CustomerID

      INNER JOIN Sales.Individual AS si

                              ON sc.CustomerID = si.CustomerID

      INNER JOIN Person.Contact AS pc

                             ON si.ContactID = pc.ContactID

WHERE ssoh.OrderDate BETWEEN @StartDate AND @EndDate

GROUP BY pc.LastName, pc.FirstName, pc.EmailAddress; 

GO

Next you will want to set up some roles: 

-- this is our internal sales personnel

CREATE ROLE internal_sales_team;

GO

-- these are sales people in the field

CREATE ROLE field_sales_team;

GO

Now you will create users for those roles: 

CREATE USER Rajendra WITHOUT LOGIN;

GO

CREATE USER Frank WITHOUT LOGIN;

GO

EXEC sp_addrolemember N'internal_sales_team', N'Rajendra';

EXEC sp_addrolemember N'field_sales_team', N'Frank';

GO

GRANT EXECUTE ON SCHEMA :: Sales TO internal_sales_team;

GO

Now test this to see how it works: 

EXECUTE AS USER = N'Rajendra';

GO

EXEC Sales.GetCustomersWithOrdersBetweenDates '20040101', '20040601';

GO

REVERT

GO

EXECUTE AS USER = N'Frank';

GO

EXEC Sales.GetCustomersWithOrdersBetweenDates '20040101', '20040601';

GO

REVERT

GO

The user ”Rajendra” can successfully execute the stored procedure, despite her user not having access to the Sales schema because she is a member of the internal_sales_team role which does have execute permissions on the Sales schema. However, the user ”Frank” cannot execute the stored procedure since the field_sales_team role does not have access to the Sales schema. If you try to run the SQL from this stored procedure as either Frank or Iris the SQL will fail since neither user has select permissions on the Sales or Person schemas. 

Through a careful combination of users, logins, roles, Windows users and Windows groups, you can assemble a very secure, robust security infrastructure in SQL Server that can handle a variety of tasks while make your administrative life a lot easier.

Most Useful SQL Performance Counters

SQL Performance Counters
Object	Counter	Preferred Value	Description
SQLServer:Access Methods	Forwarded Records/sec	< 10 per 100 Batch Requests/Sec	Rows with varchar columns can experience expansion when varchar values are updated with a longer string.  In the case where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer.  This only happens on heaps (tables without clustered indexes). Evaluate clustered index for heap tables.  In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorg pages and rows, drop the clustered index, then recreate non-clustered indexes.
SQLServer:Access Methods	Full Scans / sec	(Index Searches/sec)/(Full Scans/sec) > 1000	This counter monitors the number of full scans on base tables or indexes. Values greater than 1 or 2 indicate that we are having table / Index page scans. If we see high CPU then we need to investigate this counter, otherwise if the full scans are on small tables we can ignore this counter.  A few of the main causes of high Full Scans/sec are • Missing indexes • Too many rows requested Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time.
SQLServer:Access Methods	Index Searches/sec	(Index Searches/sec)/(Full Scans/sec) > 1000	Number of index searches. Index searches are used to start range scans, single index record fetches, and to reposition within an index. Index searches are preferable to index and table scans.  For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations.
SQLServer:Access Methods	Page Splits/sec	< 20 per 100 Batch Requests/Sec	Number of page splits per second that occur as the result of overflowing index pages. Interesting counter that can lead us to our table / index design. This value needs to be low as possible. If you find out that the number of page splits is high, consider increasing the fillfactor of your indexes. An increased fillfactor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur. Note that this counter also includes the new page allocations as well and doesn’t necessarily pose a problem.  The other place we can confirm the page splits that involve data or index rows moves are the fragmented indexes on page splits.
SQL Server:Buffer Manager	Buffer Cache hit ratio	> 90%	This counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. The higher this ratio, the less often SQL Server has to go to the hard disk to fetch data, and performance overall is boosted. Unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. In other words, this counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. In OLTP applications, this ratio should exceed 90-95%. If it doesn't, then you need to add more RAM to your server to increase performance. In OLAP applications, the ratio could be much less because of the nature of how OLAP works. In any case, more RAM should increase the performance of SQL Server OLAP activity.
SQL Server:Buffer Manager	Free list stalls/sec	< 2	Free list stalls/sec is the frequency with which requests for available database pages are suspended because no buffers are available. Free list stall rates of 3 or 4 per second indicate too little SQL memory available.
SQL Server:Buffer Manager	Free pages	> 640	Total number of pages on all free lists.
SQL Server:Buffer Manager	Lazy Writes/Sec	< 20	This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Generally speaking, this should not be a high value, say more than 20 per second or so.  Ideally, it should be close to zero. If it is zero, this indicates that your SQL Server's buffer cache is plenty big and SQL Server doesn't have to free up dirty pages, instead waiting for this to occur during regular checkpoints. If this value is high, then a need for more memory is indicated.
SQL Server:Buffer Manager	Page Life Expectancy	> 300	This performance monitor counter tells you, on average, how long data pages are staying in the buffer. If this value gets below 300 seconds, this is a potential indication that your SQL Server could use more memory in order to boost performance.
SQLServer:Buffer Manager	Page lookups/sec	(Page lookups/sec) / (Batch Requests/sec) < 100	Number of requests to find a page in the buffer pool. When the ratio of page lookups to batch requests is much greater than 100, this is an indication that while query plans are looking up data in the buffer pool, these plans are inefficient. Identify queries with the highest amount of logical I/O's and tune them.
SQL Server:Buffer Manager	Page reads/sec	< 90	Number of physical database page reads issued. 80 – 90 per second is normal, anything that is above indicates indexing or memory constraint.
SQL Server:Buffer Manager	Page writes/sec	< 90	Number of physical database page writes issued. 80 – 90 per second is normal, anything more we need to check the lazy writer/sec and checkpoint counters, if these counters are also relatively high then, it’s memory constraint.
SQLServer:General Statistics	Logins/sec	< 2	> 2 per second indicates that the application is not correctly using connection pooling.
SQLServer:General Statistics	Logouts/sec	< 2	> 2 per second indicates that the application is not correctly using connection pooling.
SQLServer:General Statistics	User Connections	See Description	The number of users currently connected to the SQL Server. Note: It is recommended to review this counter along with “Batch Requests/Sec”.   A surge in “user connections” may result in a surge of “Batch Requests/Sec”.  So if there is a disparity (one going up and the other staying flat or going down), then that may be a cause for concern. With a blocking problem, for example, you might see user connections, lock waits and lock wait time all increase while batch requests/sec decreases.
SQL Server:Latches	Latch Waits/sec	(Total Latch Wait Time) / (Latch Waits/Sec) < 10	This is the number of latch requests that could not be granted immediately. In other words, these are the amount of latches, in a one second period that had to wait.
SQL Server:Latches	Total Latch Wait Time (ms)	(Total Latch Wait Time) / (Latch Waits/Sec) < 10	This is the total latch wait time (in milliseconds) for latch requests in the last second
SQL Server:Locks	Lock Wait Time (ms)	See Description”	Total wait time (milliseconds) for locks in the last second. Note: For “Lock Wait Time” it is recommended to look beyond the Avg value.  Look for any peaks that are close (or exceeds) to a wait of 60 sec.   Though this counter counts how many total milliseconds SQL Server is  waiting on locks during the last second, but the counter actually records  at the end of locking event.  So most probably the peaks represent one huge locking event.  If those events exceeds more than 60seconds then they may have extended blocking and could be an issue. In such cases, thoroughly analyze the blocking script output. Some applications are written for timing out after 60 seconds and that’s not acceptable response for those applications.
SQL Server:Locks	Lock Waits/sec	0	This counter reports how many times users waited to acquire a lock over the past second.  Note that while you are actually waiting on the lock that this is not reflected in this counter—it gets incremented only when you “wake up” after waiting on the lock. If this value is nonzero then it is an indication that there is at least some level of blocking occurring.  If you combine this with the Lock Wait Timecounter, you can get some idea of how long the blocking lasted.  A zero value for this counter can definitively prove out blocking as a potential cause; a nonzero value will require looking at other information to determine whether it is significant.
SQL Server:Locks	Number of Deadlocks/sec	< 1	The number of lock requests that resulted in a deadlock.
SQLServer:Memory Manager	Total Server Memory(KB)	See Description	The Total Server Memory is the current amount of memory that SQL Server is using.  If this counter is still growing the server has not yet reached its steady-state, and it is still trying to populate the cache and get pages loaded into memory.  Performance will likely be somewhat slower during this time since more disk I/O is required at this stage.  This behavior is normal.  Eventually Total Server Memory should approximate Target Server Memory.
SQLServer:SQL Statistics	Batch Requests/Sec	See Description	This counter measures the number of batch requests that SQL Server receives per second, and generally follows in step to how busy your server's CPUs are. Generally speaking, over 1000 batch requests per second indicates a very busy SQL Server, and could mean that if you are not already experiencing a CPU bottleneck, that you may very well soon. Of course, this is a relative number, and the bigger your hardware, the more batch requests per second SQL Server can handle. From a network bottleneck approach, a typical 100Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need to have two or more network cards, or go to a 1Gbs network card. Note: Sometimes low batch requests/sec can be misleading.  If there were a SQL statements/sec counter, this would be a more accurate measure of the amount of SQL Server activity.  For example, an application may call only a few stored procedures yet each stored procedure does lot of work.  In that case, we will see a low number for batch requests/sec but each stored procedure (one batch) will execute many SQL statements that drive CPU and other resources.  As a result, many counter thresholds based on the number of batch requests/sec will seem to identify issues because the batch requests on such a server are unusually low for the level of activity on the server.   We cannot conclude that a SQL Server is not active simply by looking at only batch requests/sec.  Rather, you have to do more investigation before deciding there is no load on the server.  If the average number of batch requests/sec is below 5 and other counters (such as SQL Server processor utilization) confirm the absence of significant activity, then there is not enough of a load to make any recommendations or identify issues regarding scalability.
SQLServer:SQL Statistics	SQL Compilations/sec	< 10% of the number of Batch Requests/Sec	The number of times per second that SQL Server compilations have occurred. This value needs to be as low as possible. If you see a high value such as over 100, then it’s an indication that there are lots of adhoc queries that are running, might cause CPU usage, solution is to re-write these adhoc as stored procedure or use sp_executeSQL.
SQLServer:SQL Statistics	SQL Re-Compilations/sec	< 10% of the number of SQL Compilations/sec	This needs to be nil in our system as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.