Recently I was doing a pretty large SQL 2012 deployment, and was doing some research on capacity specifications.

There were lot of numbers which I found really interesting and some were really new to me. I thought it would be worth sharing them.

Maximum Database Size  – 524,272 terabytes (Both 32bit and 64bit)

Databases Per Instance – 32,767 (Both 32bit and 64bit)

File Groups Per Database – 32,767 (Both 32bit and 64bit)

Files Per Database – 32,767 (Both 32bit and 64bit)

File Size Data  – 16 TB (Both 32bit and 64bit)

File Size Log  – 2 TB (Both 32bit and 64bit)

Instances Failover Cluster  – 25 instances on a failover cluster when using a shared cluster disk as the stored option (Both 32bit and 64bit)

Nonclustered indexes per table  – 999 (Both 32bit and 64bit)

Parameters per stored procedure  – 2100 (Both 32bit and 64bit)

Parameters per user-defined function – – 2100 (Both 32bit and 64bit)

Partitions per partitioned table or index  – 15,000 (Both 32bit and 64bit)

XML indexes – 249  (Both 32bit and 64bit)

Columns per SELECT statement – 4,096 (Both 32bit and 64bit)

Columns per INSERT statement – 4,096 (Both 32bit and 64bit)

There are lot many numbers which might surprise you and the complete list is available here.

Today I opened SSMS in one of my SQL 2005 box,and to my surprise I got this error right away

“The file C:\WINDOWS\Microsoft.NET\Framework\%version%\mscorlib.tlb could not be loaded.”

I was running SQL 2005 SP3 (Yes,you heard it right !) and the only .NET framework which was installed on the box was .NET 2.0.

I went ahead to check the folder C:\Windows\Microsoft.Net\Framework and to my surprise I found that the folder framework is missing !

How did that happen ?  Before I get into identifying what caused this to happen,I had to fix the problem with SSMS.SSMS should be opened directly from the server ( I agree that its not a good practice,however the situation demanded it)

I decided to try my luck with repairing .NET framework as this was one of the options I had.I went ahead and did the repair of .NET framework 2.0 from Add/Remove options.

The repair just went smooth and I could see the missing files/ folders getting created again while the setup was running.

SSMS just launched perfect and the issue was fixed.

Conclusion

You can also do this repair in case you face similar issue.I will be focusing on why this corruption happened during the coming days and will update the same post in case I get some hints.

Thanks for reading and keep repairing !

From last night I have been doing lot of planning to setup a virtual environment on my laptop.It was kind a little complex one as I will be dealing with 3 VM’s on the laptop.One has to be setup as a domain controller and a windows cluster has to be setup and install SQL 2012 on all the VMs and do some real test on Availability groups.

Yes,some real testing on Availability Groups !

After intense planning I decided to install the very first VM using Virtual Box (https://www.virtualbox.org/).I sized the VM pretty well giving 1GB memory,25GB HDD space and of course CPU.

I mounted the ISO of Windows Server 2008 R2 SP1 and was ready to start the install process and suddenly an error came up

I was pretty sure that the laptop which I was using was having a 64bit architecture and it was a Intel core i7 processor.

What else could go wrong ?

There is one area you need to check first if you run into the similar issue,ie check if Virtualization Technology has been enabled in your BIOS.

Wow,cool isnt ?

Yes,this has to be checked out and Microsoft virtual PC has a pretty decent article which will help you to find where its available in BIOS for different laptop models.

http://www.microsoft.com/windows/virtual-pc/support/configure-bios.aspx

For my laptop this was indeed disabled by default.I enabled the same and was back to track.

I hope this quick post will help someone,some day.

Thanks for reading,and keep building virtual machines to test SQL 2012 !

Today there was an interesting question on max_files column value which is available under sys.traces view object.The question was like this  –

max_files in sys.traces is 0 and that means zero, not NULL – correct?

Before we get into the details,I would like to mention that the max_files column means the maximum number of rollover files.When we create a trace we have the option to enable file roll over,which simply means that if the first trace file size is set as 5MB,then after reaching that limit sql profiler will create another file to continue capturing the trace.This will continue until and unless the trace is stopped.

Now,can ever this max_file value be NULL ? Yes,after research I found that this value will be NULL if the is_rollover flag is set as 0(Ie,we dont choose enable file rollover when creating the trace)

select max_files,is_rollover,id AS Trace_ID from sys.traces
WHERE id <>1 --Filer DefaultTrace

Another interesting fact is related to default trace.The default trace max_file value is 5 and max_size is 20 and this cannot be changed.

Thanks for reading.

Today I came across some tools which does smart way of formatting your SQL code.Who does’nt like a code which is well formatted and easy to read.

I remember my college days when I used to write multiple lines of code for my final semester project and manually format the code for printing and ease of understanding purpose.

I would have scored better grades if I had thought about this tool long back!!! There are many tools out there,however the one which I liked well is  –

http://www.dpriver.com/pp/sqlformat.htm?ref=wangz.sqlformat.htm

You can get beautiful and well formatted codes like

BEGIN TRAN

DECLARE @endDate DATETIME

SET @endDate = Dateadd(hh, 1, Getdate()) — 1 hours from now

WHILE Getdate() < @endDate
BEGIN
INSERT INTO [Test]
VALUES      (1)

WAITFOR delay ’00:00:02′;
END

Hope you all liked this tool and happy coding!

<Update Added 12/10/2012

Today I found another tool which is pretty impressive,its http://sqlformat.appspot.com/

<Update>

Today I was doing some test cases around model database,and was querying some of the tables.I just missed to close that session and was creating a new database.

Straight away I was hit by this error

We all know that a new database inherit the model structure,however I was not aware that an exclusive lock is required for the model.

I was pretty sure that consistency is the major factor which is behind this design behavior and it was confirmed after my discussion with #sqlhelp experts.

I was able to find a connect ID too for this behavior,and Microsoft has confirmed the exact reason for this –

http://connect.microsoft.com/SQLServer/feedback/details/631177/creating-database-fails-with-error-could-not-obtain-exclusive-lock-on-database-model-retry-the-operation-later

The ID was closed with ‘By Design’ reason.

Thanks for reading.

One thing I have noticed in forums is that people are curious about features available with different flavors of SQL Server.

Most of the times questions comes like I installed SQL Server 2008 Express and find Database Mail missing,Do I need to re-install SQL Server again?

The answer is SQL Server Express doesn’t support Database Mail.

Its not really easy to remember what feature comes in with which edition,however there is a very smart and easy comparison page provided my Microsoft.

http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx 

You have the option to select multiple versions and compare the features here.Hope this post will help you answer some of the feature related questions.

Thanks for reading.

Today more and more systems are coming with NUMA Architecture and I came across this architecture when I was dealing with wait stats.

So whats is NUMA or Non-Uniform Memory Access is all about?

At this point of time we have real fast processors and its getting faster day by day. Sometimes it has to wait for the Memory which runs at a slower speed and technically this is called as Memory latency.Most of the time the CPU has to sit idle and wait for the memory.If we can reduce memory latency,then we can definitely gain performance.

The traditional Symmetrical Memory Access or SMA was having a shared memory and CPU’s accessing that memory.

NUMA refers to a memory design option available for multiprocessor systems.In NUMA architecture each processor will have a portion of memory directly attached to it.

Lets consider this simple figure which will explain the concept well –

Under NUMA architecture for multiple processor systems there will be group of processors and memory dedicated to that group and each group is called a NUMA node.

Lets taken a example of a server with 4 processor cards and each card contains 8 processor cores and 8 memory slots.Each group is called a NUMA Node.

Each CPU can access memory associated with the other groups in a coherent way too.

There was a question from one of the users on buffer storage usage.The question was something like this

‘I want to make a table into buffer storage and keep as long as I want.Any query’s can scan this table in buffer straightly?’

This was indeed an interesting question and unfortunately the answer to the question is NO.There is no way you can achieve this in newer versions of SQL Server,ie from SQL Server 2005.

Paul S Randal whom I consider as one of the best person to talk about SQL Server had talked about a DBCC PINTABLE command which actually was used in SQL Server 2000 which will pin the pages from a heavily used.

He has confirmed that this code will does nothing in SQL 2005 or above as these newer version does buffer pool management very efficiently.