SQL Server Memory Issue

Every one running SQL Server has always had the same issue of memory. Whatever the extra memory is given to windows, it is all eaten by the SQL Server alone. No matter what the process is going on, the memory takes much resources for all related to SQL Server. How can we control the memory issue. Well there are some possibilities from SQL Server that we can restrict the server to occupy the server memory.

--To check for the existing memory usage
use master
EXEC sp_configure 'max server memory (MB)'

By default the server won't display the advance options to enable this service. So follow the steps below to view the memory usage.

--To display advance option
USE master
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

After this you can run the below query to see the usage.

use master
EXEC sp_configure 'max server memory (MB)'

Then use this to change the server memory.

--To change the maximum server memory
USE master
EXEC sp_configure 'max server memory (MB)', 6144
RECONFIGURE WITH OVERRIDE

Then again you can check the usage using the query which we have see earlier. Once you are happy then you need to switch off the advanced options. And for this use the following.

--not to display advance option
USE master
EXEC sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE

Give a try!

Add a new column to every table in a database

This seems quite a painful job when so many start opening a table and add the new column name, datatype and save. But what about you have to do this for 200 tables in a database and say you have 10 databases. Hmmm.... Such a crap work doing manually but SQL Server infamiliar stored procedure comes to rescue at this point.

The below query can be used to add a new column to every single table in a database.

Use [db]
go
exec sp_MsForEachTable 'alter table ? add Load_date datetime null'

Thats it. You are done. Change the database name for the other databases.

So it is not just adding a column to all table, but modifying anything similar for each table in a database. You can change the SQL statement within the single quotes.

Best of luck.

Template explorer and Parameters

To view the template explorer click View => Template explorer.

Drag and drop any template to create a table, view or stored procedure into a new query editor window. Then click on the main menu option Query => Specify Values for Template Parameters option to specify schema name, procedure name, values for any parameters, etc.,

These values will be applied to every single place in the template query. Then the new table, view ore stored procedures can be created according to your needs.

SQL Server Editions

SQL Server comes in 2 Editions with different versions. They are categorized below.

1. Server Editions

• Enterprise (32bit, 64bit & IA64)
• Standard (32bit, 64bit)
• Standard for Small Business

2. Specialized EditionsDeveloper (32bit, 64bit & IA64)

• Workgroup (32bit, 64bit)
• Web (32bit, 64bit)
• Express (32bit, 64bit)
• Express with Advanced Services (32bit, 64bit)
• Compact (32bit)

Microsoft Certified Master - SQL Server

This blog is started with the intention of helping everyone who wants to shine in SQL Server arena. As an individual I prepare for my Microsoft Certified Masters in SQL Server and share my readings, researches, and other useful information here.

Wish you all the very best in your findings.