Setting SQL Configuration Parameters ( Memory & Locks)
Oct 27, 1998
hi, here is the situation, my system has the following specks
hard drive = 45GB
memory = 1152 mb
Opsystem = win NT 4.0
application on the NT is ftp server and SQL server 6.5
I am having a tough time retrieving a simple query from a 11 million records.some of the feed back from the newsgroup is that I do not have enought memory. Is there a formula to use to figure out how much memory should allocate to the sql server? what if I allocated too much memory, does that affect the sql performance in a negative way?
Please help......
I can be reach at a.alhussein@mci.com
thanks alot
regards
Ali
View 4 Replies
ADVERTISEMENT
Dec 13, 2001
Can someone tell me if this sounds OK?:
NT4 SP5 running SQL6.5
Physical Memory - 512mb
Virtual Memory - 1024mb
Allocated to SQL - 256mb
The largest database is 7gb and tempdb (in ram) is 150mb
Do as a rough guide these figures seem OK?
View 4 Replies
View Related
Sep 12, 2006
I have a few questions about memory setting inheritedWindows 2003, SQL Server SP4, both Enterprise Edition 8 GB RAM1. How to know how much Memory SQL can address ( SQL script is betteras there is no access to Perfmonitor or EM ) . ie what is maximummemory SQL can use ?2. In this server awe is set to 0 . Do I have to turn it to 1 so thatSQL can use all memory .3. If awe is enabled, what is the minimum and maximum memory size needto be set ?4. Should I set max worker set also ?If someone can provide examples of /PAE, /AWE. /3GB ,/USERVA etc , I amgratefulThanksSrinivas
View 3 Replies
View Related
Apr 16, 2003
I am looking for recommandations regarding the memory distribution for SQL 2000 server. In the memory tab of the server properties you can define use a fixed memory size or dynamically configure SQL server or even reserve physical memory for SQL server.
Are there any hints available or must it be handled according trial and error?
Thanks for any info
mipo
View 2 Replies
View Related
Jun 21, 2007
I overwrote rsreportserver.config with one from a different installation and that killed my RS because of the encryption key inside that file. Is there any way to generate that file for current install or do I have to reinstall the whole thing now?
Thanks
View 1 Replies
View Related
Jul 20, 2005
I am using SQL 8 Personal edition with sp2 applied. I set the maxserver memory to 32MB and leave the min server memory at 0. When myapplication starts hitting the database hard the memory usage reportedthrough task manager peaks between 41-42MB. I've stopped and restartedthe MSSQLserver service and checked that the running values are what Iset them to be. Does anybody have any ideas as to why the sqlservr.exewould be utilizing more memory than the configured value?This message was posted some years ago but nobody answered. Now I havethe same problem! Does anybody have an explanation?Thanks a lot
View 4 Replies
View Related
Nov 4, 2007
I need to up the Maximum Memory setting.
Out of the box it is set to 2147483647 MB.
I assume this is ~2GB. I have been instructed
to set it to 1GB less than total server memory
which is 16GB. When I change the value
it just changes back.
How do I change it and how are these Megabyte
numbers calculated?
OS: Windows 2003 R2 Enterprise
SQL: SQL Server 2005 Standard
Thanks!
View 2 Replies
View Related
Apr 19, 2000
hi
I want to ask the method to calculate the memory in the configuration in SQL6.5.
If I have 256MB memory installed in the server, how many memory needed in the configuration of SQL6.5.
View 1 Replies
View Related
Oct 26, 1998
I have corrected the problem with named pipes versus TCP/IP but I have found a reference to lrustats stating that page flushes should be less than 100 and free avg less than 10 but my numbers are extremely high. One of the things I noticed upon assuming this job was that many settings were off a bit bit LogLRU buffers were never set.This setting was introduced with service pack 2 with a readme.txt on how to configure. All my references and the ones that I have skimmed through in bookstores do not contain this configuration setting.
Microsoft no longer offers SP2 for download so that I could get the readme.txt. If any of you have this file or service pack laying around, please email it to me so that I can configure. It is suppose to take care of some bufwait errors. I even download the French version to see if it was there and maybe I could find someone with a french background but it did not contain the file. It was in last years Technet CD but my company tossed it when they got the new one. Can anyone help?
View 1 Replies
View Related
Feb 22, 2015
I have an issue with my production server regarding memory usage (Memory Utilization is above 95%.). Memory is : 12 GB and the service that is consuming the majority of memory 88%/10.5GB is sqlserver.exe. So it would appear that MSSQL is not set to restrict the amount of memory it uses ? How much should I set for min and max memory ? the defauld is min memory : 0 and max : 2 TB
View 5 Replies
View Related
Dec 21, 2007
I am running SQL 2005 Standard under Windows 2003 Standard with 8 GB of RAM in one of our 64 bit servers; I noticed min server memory was set for some reason to 128 MB and max server memory to 6114 MB by the previous DBA. We plan to upgrade it to 32 GB. What is the best approach when changing those values ? Should I change those values to the defaults (0 - 2147483647) or create min and max boundaries? . This box is only utilzed by SQL.
Also I know that those changes are reconfigured dynamically so no need to reboot, but would this place any performance hit in the server and should I use Management studio or sp_configure ? (for some reason I don't trust Management studio for these type of changes :))
Thanks,
Carlos
View 1 Replies
View Related
Jun 5, 2006
Hello,
So we have a clustered environment on Windows 2003.
We have 12GB ram, and are running with /3GB and /PAE.
We do not currently have AWE enabled.
SQL is currently using 2.799GB
This leaves only 200MB or so free for my COM+ component, and if/when it raises to that level, application users get 'Out of Memory' error.
How can we get these two products to share memory better, and more importantly utilize more of the free memory on the system.
Should we have AWE enabled? Are there any negatives to this? If AWE is enabled and SQL Uses say 6GB, Will the COM+ application have 0 available?
View 3 Replies
View Related
Nov 3, 2006
I have a client server that has win2000 on it with sql 2000 enterprise edition. The box has 4 gig of memory on it. I noticed today that the sql server was set to use all 4 gig (even though I know sql can't really access that memory because sql can't really utilize the 2-4 gig range). Is there overhead or a downside to leaving it at this, or should I set it to 2 gig
View 3 Replies
View Related
May 21, 2015
I am curious about maximum memory setting .
Should we set maximum memory setting for each SQL server? For example a server has 6 GB memory then should we set maximum memory setting = 3.5 GB ?
View 9 Replies
View Related
May 4, 2007
We have a SQL 2005 installed in Windows 2003 64 bit system, which has 16 CPU and 32GB RAM, but the performance is poor. SQL server is AWE enable and the sql start account with "Lock Pages In Memory". I checked the task manager and it looks that the SQL server used only about 300 MB memory. Here is what I found:
http://blogs.msdn.com/psssql/archive/2006/11/28/sql-server-becomes-sluggish-or-appears-to-stall-on-64-bit-installations.aspx?CommentPosted=true#commentmessage
What I need to do?
Thanks
View 5 Replies
View Related
Dec 4, 2006
I am unable to initialize the reportserver: gettting the folowing error:
is it possible to reinitialize the server:
***********************************************************************************
The encrypted value for "logoncred" configuration setting cannot be decrypted.
(rsFailedTodecryptConfigInformation)
***********************************************************************************
Please help thank you very much for the information.
View 9 Replies
View Related
Jul 16, 2015
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
View 9 Replies
View Related
Sep 18, 2014
We are just finishing our migration to SQL 2012. In our old environment, the instance which held our SharePoint databases also served other applications. We did not experience any performance related issues in the past due to this.
SharePoint basically requires MAXDOP to be 1, which is correct on the old server. Since this configuration may not be ideal for other applications that may be put within our environment, we our entertaining the idea of isolating SharePoint into its own instance, probably on the same box.
My manager wants me to come up with performance trace data to better prove that we need to go this route since we apparently have had issues in the past by blindly following Microsoft's best practices.
1.MAXDOP configuration - I understand this may be a 2 pronged approach that would require looking at various execution plans and CPU related counters in Perfmon. SharePoint likely requires a maxdop of 1 due to the nature of the application (lots of concurrent processes). What is the best way to show this need graphically?
2. Memory configuration for multiple instances - Does the Total Server Memory reveal all the memory that a given SQL instance is utilizing? Should I use this counter to identify appropriate min/max memory configurations for multiple instances on a single cluster?
The problem with the perfmon approach is that it's scope is limited to just the server. Since our SharePoint environment is currently being shared with other applications, I understand that I may have to utilize DMV statistics to narrow down my analysis.
View 3 Replies
View Related
Apr 29, 2015
After I have set the maximum sql server memory in sql server 2014 using sp_configure, do I need to reboot whole server or restart sql service?
View 4 Replies
View Related
Aug 5, 2015
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
View 7 Replies
View Related
Aug 29, 2007
Hi,How do I set the parameters of an SqlDataSource programatically?I have tried the following...dsDraftBudgetPI.SelectParameters.Add(new Parameter("@person_id", TypeCode.Int32, pID.ToString()));But that didn't work. I already have the parameter defined at design time so I don't need to create one I just want to set its value and then bind it to a ListBox to display the result.Thanks,Scott
View 3 Replies
View Related
Jan 24, 2006
Here is the code I am trying to use to set a update parameter at runtime. (Depending on what linkbutton a user clicks on the STATUS_ID value will change.)
SqlDataSource1.UpdateParameters("STATUS_ID").DefaultValue = 33332Here are my parameters:
<UpdateParameters>
<asp:Parameter Name="CUSTOMER_ID" Type="Decimal" />
<asp:Parameter Name="RECEIVED_BY" Type="String" />
<asp:Parameter Name="CALL_DATETIME" Type="DateTime" />
<asp:Parameter Name="AREA_ID" Type="Decimal" />
<asp:Parameter Name="CLASS_ID" Type="Decimal" />
<asp:Parameter Name="STATUS_ID" Type="Decimal" /></UpdateParameters>Sqldatasource1 is the name of my datasource control. Any thoughts?
View 2 Replies
View Related
Aug 3, 2007
Hi,
I am testing SQL 2005 Standard (32 bit) on a Windows 2003 Server 64 bit with 8GB of RAM? Should I enable the AWP Setting or not and should I change the maximum server memory (currently saying 2GB)?
Thanks!
Tom
View 1 Replies
View Related
Dec 3, 2007
We have several 2005 servers with "Maximum server memory" set to 214 gig, which I believe is the default at installation time. I am told that this means "use all the memory there is including paging." Well, this is nuts but the servers seem to work fine with this setting no matter how much physical memory they have.
One of our 2005 servers recently started paging like crazy, so I reduced "Maximum server memory" to 6000 and the paging disappeared (server has 8 gig of physical memory) and the server appears happy.
I can not explain why only this one server has this paging issue and the others do not. Should I be setting "Maximum server memory" on all my servers? Are there other considerations which might cause the server to eat-up all the memory? As far as I know no other applications run on this box.
Thanks,
Michael
View 6 Replies
View Related
Aug 9, 2007
I would like to know what happens when a very large reference data set for a lookup transform with full caching enabled is getting loaded during package execution and the computer memory runs out or is very low.
Does SSIS
a) give an out of memory error of some sort
b) resort to a no caching or partial caching mode
c) maintain the full caching mode but will switch to using the paging file(virtual memory).
I think it will resort to using the page file in which case the benefits of in memory lookups are lost and performance would suffer. If I cannot upgrade the memory or shrink the reference set somehow, i should switch that lookup task to use partial caching or no caching with an indexed lookup table. Would this make sense?
View 1 Replies
View Related
Dec 26, 2007
I have an SQLDataSource. The SQL is
SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records.
e.Command.Parameters("@Printed1").Value = Nothing 'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False
What am I doing wrong???
Thanks
Craig
View 10 Replies
View Related
Feb 17, 2008
I have a sqlDatasource with 3 parameters based on the input of 3 text boxes on the page. The datasource returns sales details for a company based on a from and to date. I am attempting to set the value of the 3 parameters in the Selecting event of the datasource control but I'm not getting any data back. If I set the values literally then I get data back. Also when I step through the code I can see the 3 parameters getting their values from the textboxes and the drop down list as they should. This is driving me insane as I'm new to .net and just can't see what is stopping me retrieving the data when using the form fields to set the datasources parameters. Below is the aspx and the code behind for the page. Thanks in advance for any help. aspx..... <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="LabelSales.aspx.cs" Inherits="LabelSales" Title="Untitled Page" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:SqlDataSource ID="SqlDataSourceSales" runat="server" EnableCaching="false" ConnectionString="<%$ ConnectionStrings:streetwisedigitalConnectionString %>" SelectCommand="DL_GET_SALES_BY_LABEL" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSourceSales_Selecting" > <SelectParameters> <asp:Parameter Name="fromDate" /> <asp:Parameter Name="toDate" /> <asp:Parameter Name="label" /> </SelectParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSourceLabels" runat="server" SelectCommand="select label_id, label_name from dl_label order by label_name asc" ConnectionString="<%$ConnectionStrings:streetwisedigitalConnectionString%>"> </asp:SqlDataSource> <div> <table> <tr> <td>Start Date: </td> <td> <asp:TextBox ID="FromDate" Runat="server" Width="70"/> <asp:Button ID="btnFrom" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>End Date: </td> <td> <asp:TextBox id="ToDate" Runat="server" Width="70" /> <asp:Button ID="btnTo" Runat="server" Text="..." UseSubmitBehavior="false" /> </td> </tr> <tr> <td>Label: </td> <td> <asp:DropDownList ID="LabelList" Runat="server" DataSourceID="SqlDataSourceLabels" DataTextField="label_name" DataValueField="label_name"> </asp:DropDownList> </td> </tr> <tr> <td colspan="2" align="right"> <asp:Button ID="Button1" OnClick="SubmitButton_OnClick" Text="View Sales" runat="server" /> </td> </tr> </table> <p> <asp:GridView ID="GridViewSales" runat="server" DataSourceID="SqlDataSourceSales" ShowFooter="True" AllowSorting="True" AutoGenerateColumns="False" OnRowDataBound="GridViewSales_RowDataBound" EmptyDataText="No data to display."> <Columns> <asp:BoundField DataField="cat_no" HeaderText="Cat No" /> <asp:BoundField DataField="artist" HeaderText="Artist" /> <asp:BoundField DataField="title" HeaderText="Title" /> <asp:BoundField DataField="remix" HeaderText="Remix" /> <asp:BoundField DataField="qty" HeaderText="Sold" /> <asp:boundfield datafield="commission" HtmlEncode="False" dataformatstring="{0:F2}" headertext="Commission"> <ItemStyle HorizontalAlign="Right" /> </asp:boundfield> </Columns> </asp:GridView> </p> </div></asp:Content>code behind...using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class LabelSales : System.Web.UI.Page{ int totalSold; decimal totalCommssion; protected void GridViewSales_RowDataBound(object sender, GridViewRowEventArgs e) { // add column totals to gridview if (e.Row.RowType == DataControlRowType.DataRow) { totalSold += (int)DataBinder.Eval(e.Row.DataItem, "qty"); totalCommssion += (decimal)DataBinder.Eval(e.Row.DataItem, "commission"); } // display the totals else if (e.Row.RowType == DataControlRowType.Footer) { e.Row.Cells[0].Text = "<b>Total</b>"; e.Row.Cells[4].Text = totalSold.ToString(); e.Row.Cells[5].Text = totalCommssion.ToString("f2"); } } protected void Page_Load(object sender, EventArgs e) { } protected void SqlDataSourceSales_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { // *** This method does not work *** e.Command.Parameters[0].Value = FromDate.Text; e.Command.Parameters[1].Value = ToDate.Text; e.Command.Parameters[2].Value = LabelList.Text; // *** This method works! *** e.Command.Parameters[0].Value = "02/01/2007"; e.Command.Parameters[1].Value = "02/01/2008"; e.Command.Parameters[2].Value = "Fat!"; } protected void SubmitButton_OnClick(object sender, EventArgs e) { SqlDataSourceSales.Select(DataSourceSelectArguments.Empty); }} Many thanks Simon
View 3 Replies
View Related
Feb 5, 2008
Hi All,
I am creating the report and setting the datasource(dataset) dynamically to the report. I want to set the parameters also dynamically. am using Report Viewer to process the report.
When i set the parameters locally, i am getting the below error:
An error occurred during local report processing
Here is my code:
da = new SqlDataAdapter(strqry, conn);
ds = new DataSet();
da.Fill(ds);
ReportDataSource ReportDataSourceX = new ReportDataSource();
ReportDataSourceX.Value = ds.Tables[0];
ReportParameter[] parm = new ReportParameter[2];
parm[0] = new ReportParameter("Business_Function", "SQMO");
parm[1] = new ReportParameter("Application", "ETMRS");
parm[0] = new ReportParameter("Owner", "Subbu");
//parm[0] = new ReportParameter("Business_Function", ds.Tables[0].Columns["Business_Function"].ToString());
//parm[1] = new ReportParameter("Application", ds.Tables[0].Columns["Application"].ToString());
//parm[0] = new ReportParameter("Owner", ds.Tables[0].Columns["Owner"].ToString());
RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;
RptViewer.LocalReport.ReportPath = "C:\ETMRS Reporting\ETMRS Reports\ETMRS Reports\PlannedTestCasesSummary.rdl";
//RptViewer.LocalReport.SetParameters(parm);
RptViewer.LocalReport.DataSources.Add(ReportDataSourceX);
RptViewer.LocalReport.SetParameters(parm);
RptViewer.LocalReport.Refresh();
Can anyone please help me in resloving this issue.
Thanks in advance,
SR.
View 9 Replies
View Related
Mar 10, 2008
I'm trying to figure out how I might be able to enable IP1 and IP2 through something like Visual Basic.
I've looked into SMO, I don't see that it lets you access that level of configuration. Is there any way to set configuration values like this other than through the GUI?
View 2 Replies
View Related
Apr 24, 2007
Does anyone know how to set multi-value parameters using ReportExecutionService in C#?
If I have
rsExec = new ReportExecutionService2005.ReportExecutionService();
.
.
.
ReportExecutionService2005.ParameterValue[] parameters = new ReportExecutionService2005.ParameterValue[2];
parameters[0] = new ReportExecutionService2005.ParameterValue();
parameters[0].Label = "Report_Begin_Date";
parameters[0].Name = "Report_Begin_Date";
parameters[0].Value = "4/15/2007";
parameters[1] = new ReportExecutionService2005.ParameterValue();
parameters[1].Label = "SalesID";
parameters[1].Name = "SalesID";
parameters[1].Value = ??? //Here, this parameter should accept multiple values like 200, 201, 202, etc.
rsExec.SetExecutionParameters(parameters, "en-us");
How would I set the SalesID to take multiple values?
View 12 Replies
View Related
Mar 11, 2008
Hello.
I have one dataset with a few parameters.
Each parameter get its available values from the some dataset.
So if I have the main DS whice is:
"select name,age,sex from table1 where age = @age and sex = @sex"
The available values for the parameters "age" and "sex" are those datasets:
"select distinct age from table1" and
"select distinct sex from table1".
The problem is thet the query on table1 takes a lot of time and in this case I need to run her three times.
Is there a way to initialize the parameters values in one query excution and save some time?
Thanks.
View 6 Replies
View Related
Jun 13, 2007
I am trying to get familiar with Microsoft neural networks to predict property prices. The results are better but I wanted to amend the default parameters passed to the neural network.
So on MINING MODEL TAB when I right click and go into SET ALGORITHM PARAMETERS, I can't see any parameters there, if I try to enter a parameter for example MAXIMUM_STATES and process the model I get the following error message
"The 'maximum_states' data mining parameter is not valid for the 'My Model' model"
I also added a decision tree model to the same structure and when go into SET ALGORITHM PARAMETERS pop menu it comes with many pre populated parameters with default values.
My question is that why I am unable to add parameters to the NEURAL NETWORK and why it does not come with pre populated parameters like DECISION TREES.
Your help will be much appreciated.
View 3 Replies
View Related
Nov 7, 2007
I would like to set up a subscription that has two date parameters, I would like the end_date to be today and the start_date to be (today - 1 Month). The interface does not seem to support expressions?
I saw some documentation that said to use defaults in the report but that does not help because I may want multiple subscriptions with different params like 1 person may want (today - 2 months) as the start date ...
Any help would be appreciated
View 9 Replies
View Related
