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
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
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?
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?
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
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
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.
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?
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
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 :))
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
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:
The encrypted value for "logoncred" configuration setting cannot be decrypted. (rsFailedTodecryptConfigInformation) ***********************************************************************************
Please help thank you very much for the information.
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:
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.
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.
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.
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
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?
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)?
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.
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?
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
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
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());
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?
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.
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 ...
