You can limit the amount of memory that SQL Server uses by using the sp_configure stored procedure. By limiting the amount of memory that SQL Server is permitted to use it means that more memory is available to other applications or other instances of SQL Server. In fact books on-line recommends setting the minimum and maximum memory used on each instance of SQL Server running on the same machine as SQL Server does not make any attempts to balance memory usage across instances.
In order to use this you must be in an advanced mode. To set this up use:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
Next, to make the actual change you need the following:
EXEC sp_configure 'max server memory (MB)', 512
RECONFIGURE WITH OVERRIDE
The above example will set the maximum amount of memory the server will use to 512MB. The RECONFIGURE WITH OVERRIDE is necessary in order for the change to take effect immediately. If it is missed out then the change won't take place until the SQL Server is restarted.
If you want to check that the change has taken place you can use the following:
EXEC sp_configure 'max server memory (MB)'
This will just display the current setting. You will get a result set that looks something like this:
The congif_value is the value that the SQL Server is currently configured with. However, it may not be what is currently in force. The run_value shows you what is currently in force.
If you don't want to type so much SQL yourself, then you can do the same in the SQL Server Management Studio. Right-click the server in the object explorer and select "properties" from the context menu. This will bring you up a dialog with all the server level properties in it. Go to the "memory" page and you can set the values that you want there. There are a couple of radio buttons that will allow you to switch between the currently configured value and the running value. By pressing Okay the updated value is applied to the server immediately.
For more information:
Technorati Tags:
sql server,
memory
This is the 5th part in a series on LINQ to XML. In this instalment we will look at monitoring changes in XML data in the XML classes added to .NET 3.5.
The XObject class (from which XElement and XAttribute, among others) contains two events that are of interest to anyone wanting to know about changes to the XML data: Changing and Changed
The Changing event is triggered prior to a change being applied to the XML data. The Changed event is triggered after the change has been applied.
An example of adding the event handler would be something like this:
XElement root = new XElement("root");
root.Changed += new EventHandler<XObjectChangeEventArgs>(root_Changed);
The above example will trigger for any change that happens in the node the event handler is applied to and any node downstream of it. As the example is applied to the root node this means the event will trigger for any change in the XML data.
The event handler is supplied an XObjectChangeEventArgs object which contains an ObjectChange property. This is an XObjectChange enum and it lets the code know what type of change happened.
The sender contains the actual object in the XML data that has changed.
Adding an element
Take the following example where an element is added to the XML data.
XElement child = new XElement("ChildElement", "Original Value");
root.Add(child);
In this case the ObjectChanged is Add and the sender is the XElement: <ChildElement>Original Value</ChildElement>
A similar scenario happens when adding an attribute. However, instead of the sender being an XElement it will be an XAttribute.
child.Add(new XAttribute("TheAttribute", "Some Value"));
Changing an element value
If the value of the element is changed (the bit that currently says "Original Value") then we don't get one event fired. We get two events fired. For example:
child.Value = "New Value";
The first event with ObjectChanged set to Remove and the sender set to "Orginal Value" (which is actually an XText object) and the second event with the ObjectChanged set to Add and the sender set to "New Value" (again, this is actually an XText object).
Changing an element name
If the name of the element is changed then the ObjectChanged property will be set to Name and the sender will be the XElement that has changed.
child.Name = "JustTheChild";
Changing an attribute name
Unlike changing an element value, when the value of an attribute changes the ObjectChanged property will be Value and the sender will be the XAttribute.
child.Attribute("TheAttribute").Value = "New Attribute Value";
Technorati Tags:
c#,
.net,
.net 3.5,
xml
From .NET 2.0 onwards a new and improved configuration management system has been put in place. You can now add a <connectionString> element to the config file and use it to place the connection strings to the database and then retrieve then in a consistent way in your application. It supports multiple connection strings too if you need to access multiple databases.
The config file looks like this:
<configuration>
...
<connectionStrings>
<add name="Default" connectionString="Server=(local);database=MyDatabase"/>
</connectionStrings>
...
<configuration>
From the .NET application you can access the connection string like this:
connectionString =
ConfigurationManager.ConnectionStrings["Default"].ConnectionString;
Just remember to add a reference to System.Configuration in your project and ensure that the code file is using the System.Configuration namespace as well.
SQL Bits are going for a slightly different take on the registration process this time. You vote for the sessions you want to see at the same time as signing up for the conference. This is quite interesting because it means that you are signing up as a delegate without actually knowing what the final agenda will be. It's quite exciting really!
If you want to vote on the sessions you want to see and sign up for the conference then you can head on over to the SQL Bits website and sign up. Remember to vote for my session while you are there: "Where's my data? An introduction to Spatial Queries in SQL Server 2008"