Stored Procedures Cache

The stored procedure cache is an area of memory where SQL Server keeps the compiled execution plans. Once a stored procedure has been executed, the execution remains in memory, so the subsequent users, each with a different execution context (including parameters and variables) can share one copy of the procedure in memory. SQL Server 2000 has one unified cache, where it stores data pages with the stored procedures and queries plans. Because SQL Server 2000 can dynamically allocate more memory when it is needed, the execution plan can stay in the cache as long as it is useful. However, if there is not enough memory for the current SQL Server work, the older plans could be deleted from the cache to free up memory.
Each execution plan has an associated cost factor that indicates how expensive the structure is to compile. The more expensive the stored procedure is to compile, the larger the associated cost factor it will have, and vice versa. Each time the stored procedure is referenced by a connection, its cost factor is incremented. Therefore, a cached plan can have a big cost factor when the object is referenced by a connection very frequently or when recreation of the execution plan is very expensive. The lazywriter process is responsible for determining whether to free up the memory the cache is using, or keep the plan in cache. This process periodically scans the list of objects in the procedure cache. If the cost of a buffer is greater than zero when the lazywriter inspects it, the lazywriter decrements the cost factor by one.
Every time a cached plan is reused, its cost reverts to its original value. The lazywriter process deletes the execution plan from the cache when the following three conditions are met:
The memory is required for other objects and all available memory is currently in use.
The cost factor for the cached plan is equal to zero.
The stored procedure is not currently referenced by a connection.
The frequently referenced stored procedures do not have their cost factor decremented to zero and are not aged from the cache. Even though the plan's cost factor will be equal to zero, it will stay in cache unless memory is required for other objects.
When you tune your stored procedures to maximize performance, you may want to clear the stored procedures cache to ensure fair testing. In this case, you can use the DBCC FREEPROCCACHE statement to clear the procedure cache.

Can't Delete a Column from SQL Server

I was working on Sql server 2000 which was migrated from older version to sql server 2000. I have added a column to an existing table using the following query: alter table test add col1 varchar(1) And afterwards I wanted to delete that column. I have tried the following query for that alter table [test] drop column [col1] but its always giving me the following error message : This query executed successfully. 'test' table - Unable to modify table. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'col1'. Same error I was getting when I tried deleting the column from Enterprise Manager. Resolution : first run the following query & check the compatibility : sp_dbcmptlevel databaseName If the compatability level is 65, change it to 70 or 80 by using the following query: sp_dbcmptlevel databaseName,70 Now the delete query will be executed without fail.

Upload File

IOTool webIOTool = new IOTool();
FileStream liveStream = new FileStream(fdAttachement.FileName, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[(int)liveStream.Length];
liveStream.Read(buffer, 0, (int)liveStream.Length);
liveStream.Close();
webIOTool.CreateAttachment(CT.GetConfigVal("TaskAttachmentPath") + fileName, buffer);



public class IOTool : System.Web.Services.WebService
{
#region Web Methods
///

/// Creates the file.
///

/// The File Path
/// Empty string or error message if any
[WebMethod]
public string CreateAttachment(string destFilePath, byte[] buffer)
{
try
{
Stream istream;
istream = File.Create(destFilePath);
istream.Write(buffer, 0, buffer.GetUpperBound(0));
istream.Flush();
istream.Close();
}
catch (Exception ex)
{
return ex.Message;
}
return string.Empty;
}
}

Loading and Saving File

OpenFileDialog fdAttachement = new OpenFileDialog();
fdAttachement.Filter = "All Files (*.*)*.*Text files (*.txt)*.txtWord Files (*.doc)*.docExcel Files (*.xls)*.xlsPDF Files (*.pdf)*.pdfImage Files(*.jpg,*.gif)*.jpg;*.gif";
//fdAttachement.InitialDirectory = "";
fdAttachement.DefaultExt = "*.*";
if (fdAttachement.ShowDialog(this) == DialogResult.Cancel)
return;
FileInfo fileInfo = new FileInfo(fdAttachement.FileName);
string[] fullName;
string fileName;
fullName = fdAttachement.FileName.Split('\\');
fileName = fullName[fullName.Length - 1];
fileInfo.CopyTo(CT.GetConfigVal("TempTaskAttachmentPath") + fileName, true);

How to open file

System.Diagnostics.Process.Start(strFileName);