SQL Server – Capacity Planning at Intial stage..!

What is capacity planning?

Capacity planning is used to determine the ever changing needs of an application pro actively. In words of SQL Server, capacity planning of SQL Server is used to determine the capacity of SQL Server , which is used to determine the forthcoming needs of  SQL Server  corresponding to the  growth of business , data, users etc.

Capacity Planning while intial deployment?

Recently when one of my friend Santhosh Balakrishnan  who is my colleague in Allianz Managed Operations and Services , opens a question to me regarding the intial capacity planning of SQL Server. The question was “How we will Configure  or do the Capacity planning  during the intial setup?”.  I think question was wonderful, because it makes sense.

The answer to Santhosh question   from my point of view is we can collect the requirements from the business for example no of users  connect to the system in next 5 or n  years , dump data etc and do a load testing for SQL Server. Form the results of Load testing we can will be able to estimate a round figure of what we need for the SQL Server for running in the tested Work load.

Capacity Planning for SQL Server suggestions for Microsoft

Here is the link of the article by Microsoft regarding the SQL Server Capacity Planning. So I am not going write in detail about the SQL Server Capacity Planning. I will try include some scripts for finding the capacity planning in the coming posts.

http://technet.microsoft.com/en-us/library/cc298801%28office.14%29.aspx

I like to dedicate this post  to my dear friend Santhosh Balakrishnan.

SQL SERVER – VIEWS  and its Limitations

SQL SERVER – VIEWS and its Limitations

View is a virtual table whose contents are defined by a query.  Why we are using views?

The Answer is,

  • A security mechanism to allow users to access the data through views, without granting permissions to users to access the underlying tables.
  • Complex queries can re-write to view, so that we can use simple queries to extract the data.
USE [MyExperiments]
GO
 
/****** Object:  Table [dbo].[EmployeeDetails]    Script Date: 01/11/2012 05:43:47 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[EmployeeDetails](
	[ID] [int] NOT NULL,
	[Name] [varchar](50) NULL,
	[EmpType] [bit] NULL,
	[Designation] [varchar](50) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Now I am inserting some data into the table.

--Inserting Data into the table
insert into EmployeeDetails
Select 1,'praveen',1,'DBA'
union all
select 2,'kiran',1,'juniorDBA'
union all
select 3,'Fazal',0,'QA Analyst'
Creating a view for this table.
 
--Creating a view
 
Create view vw_EmpNameDesignation
as
select name,Designation from EmployeeDetails
GO

Encryption option to restrict the view details:
If we want to secure metadata of the view, we can use Encryption option. We can restrict the users to see the create view statement from sys.syscomments
Before Encryption:
Here I am issuing to view the metadata
Select * from sys.syscomments

 

Applying Encryption option:
Here I am altering the view with Encryption option.

 

--Encrypting  the view - Query statement
Alter  view vw_EmpNameDesignation with Encryption
as
select name,Designation from EmployeeDetails
GO
 
select * from sys.syscomments 
GO

 

 

Some of the Restriction of views:

  • We should enable ANSII_NULL ON
  • We should enable QUOTED_IDENTIFIER ON
  • You cannot perform outer joins
  • Indexed view should be created with schema binding option
  • Repeated columns cannot use in select list of views.
  • AVG, MAX, MIN, STDEV, STDEVP, VAR, and VARP are not allowed.
  • We can’t use ROLLUP, HAVING and CUBE in Group by clause.