I'm working on a SQL Server 2005 database (under Win 2003) which is used for a web application (24/24) and on the other hand for asynchronous works (20h -> 06h).
Sometime the asynchronous works consume all system resources and have for consequence to slow the web application even unavailable.
So I want to install a soft-NUMA configuration in order to limit the CPU access for the asynchronous works.
The test platform is based on two hardware NUMA nodes, each having 2 hyperthreading CPU (4 CPUs per node for Windows). The repartition of the CPU on the two hardware NUMA node is :
- Node 0 : CPU 0, 1, 4 et 5
- Node 1 : CPU 2, 3, 6 et 7
Actually, I'm testing the following configutration on the test platform that is discribed higher :
Configuration soft-NUMA (in the registry)
CPU 7 6 5 4 3 2 1 0
node 0 0 0 1 1 0 0 1 1
node 1 0 0 0 0 1 1 0 0
node 2 1 1 0 0 0 0 0 0
I respected the fact that this is not possible to create soft NUMA nodes with CPU which are on differents hardware NUMA nodes.
In parallel, any CPU affnity is defined because I am on a single database instance.
Then, on SQL Server, I defined an endpoint on the TCP port 2000 by taking care well to give connction right on this endpoint.
At the end, I added in the SQL Server Configuration Manager the TCP port 2000 with a soft-NUMA mapping in order to create grouping. I obtained the following configuration : 1433,2000[5].
With this configuration, normally, when I'm connected to the database by the TCP port 1433, I have all the CPU. In the other hand, when I'm connected by the TCP port 2000, I have just the CPUs of the NUMA nodes 0 and 2 (CPU 0,1,4,5,6 and 7)
However, and this is my problem, when I'm connected by the TCP port 2000 and that I execute a query which I know it consumes all CPU resources, all CPU are used !
In order to verify, I changed the mapping in the TPC port like this : 1433[2],2000[5].
Normaly, with a connection by the TCP port 1433, I have just the CPU of the NUMA node 1. However, all CPU are used by my query.
When I'm looking at the configuration which is in the error log, I don't see anything strange :
Multinode configuration: node 0: CPU mask: 0x0000000c Active CPU mask: 0x0000000c. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.Multinode configuration: node 1: CPU mask: 0x00000033 Active CPU mask: 0x00000033. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.Multinode configuration: node 2: CPU mask: 0x000000c0 Active CPU mask: 0x000000c0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.Server is listening on [ x.x.x.208 <ipv4> 2000].Server is listening on [ x.x.x.245 <ipv4> 2000].Server is listening on [ x.x.x.132 <ipv4> 2000].SQL Network Interfaces initialized listeners on node 2 of a multi-node (NUMA) server configuration with node affinity mask 0x00000005. This is an informational message only. No user action is required.Server is listening on [ x.x.x.208 <ipv4> 1433].Server is listening on [ x.x.x.245 <ipv4> 1433].Server is listening on [ x.x.x.132 <ipv4> 1433].SQL Network Interfaces initialized listeners on node 1 of a multi-node (NUMA) server configuration with node affinity mask 0x00000002. This is an informational message only. No user action is required.
Is someone know why, in spite of the NUMA configuration, all CPU are used ?
Is there a configuration error ?
Is it necessary to define an affinity CPU in parallel of NUMA configuration ? But how to make, because I work on only one database ?
What's new with NUMA in SQL Server? Is there any way to get a reading, from SQL Server or perfmon or hardware vendor tools or VM tools, of how much you are crossing NUMA boundaries, and what that is costing? The only "NUMA management" trick I know is setting maxdop at or below the number of cores on a physical processor. I guess the rest is supposed to be automagic.Is there anything else one can see or do regarding NUMA?
hi,,,here is the issueon the site there is a multiline text box.people type in muliple lines including soft line breaks(soft return (shift+enter)) and hard line breaks (hard return(enter)). the data gets stored in the SQL server 2000 database. In enterprise manager I can see the returns (the line breaks), but when the same data gets posted back to the page it loose those soft and hard returns,whats going onhelp
A bit of a double question. We are setting up a new environment but this time we don't want to use the build-in replication. As we have a very changing environment these replications block us in modifying tables. (Was on W2K/Sql2000).
To avoid this we would write a softreplication, now we are running on W2003AS/Sql2005Ent, but when trying to run a transaction to an older server this error pops up. "The transaction manager has disabled its support for remote/network transactions".
I've found some articles on how to solve this but they all mention a buttin 'security config' on the MSDTC tab page of MyComp in CompServ. But i can't see that Button on a W2k.
Any suggestions ..
Or, 2nd question, is it still so that when a replication has been setup in 2005 that bthe table is unmodifiable ??
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.
===================================
The following components are installed on this server
Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do
thanks in advance Dave
Background:
SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go
USE [ETLConfiguration] GO /****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SSIS Configurations]( [ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL, [PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL, [ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
there are two tables involve in replication let say table1 and replicated table is also rep.table1.
we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.
plz let me know where am i wrong and how i put it into right way.
there is no triggers on published tables and noother trigger is created on published table.
It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.
Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.
It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)
I am creating a windows application to manage my ssis packages.
My SSIS packages use package configurations, and by default connect to the "SSIS Configurations" table in sql server.
However, I want it to connect to a view instead of this table, so i create a view "vwSSIS_Configurations" and point the package configuration to use the view in sql server.
The application SHOULD be capable of altering the package configuration's configuration and switch its connection between the default "ssis configurations" table, and the view.
Is this possible? I cannot think of a way which I can have my application switch the package configuration's connection.
I have a Sql7.0 box inside a firewall and Sql6.5 outside the firewall. I would like to use a DTS to transfer data. I followed the KB artice on configuring SQL to listen on a specfic port, but still have problems. I added entries in Client Config on both machines. Still no luck, looks like the 7.0 box not talking on new port...
I have a question about SQL Server configurations, especially those related to memory.
We are running SQL 6.5 SP5a on a 400 MHz machine with 1GB of RAM.
Our memory setting is 409600 (800 MB), which according to all the documentation that I have read is a conservative figure. The problem I believe is in our settings for Locks and Open Objects. The settings that are in place are Locks=3000000 and Open Objects=500000.
I am not a DBA, I am a developer, but have somehow become part of the DBA team. These settings seem out of proportion with the memory we have. The reason given for 3 million locks, by other members of the team, is that our biggest unit of work is a query that performs a select into of approx. 3 million records. To further compound things, we are getting an out of memory error on the server after only a day of running.
The other members of the team have implemented a start and start of the service to occur at 4 AM every morning, but I am convinced that this is not the appropriate action, but they will not action any configuration changes unless I can prove that it will help the memory problem and not effect the queries.
If anyone has any information or links to information regarding Locks and how they relate to query performance, or at least a sample of what your settings are it would go a long way towards convincing either myself or the rest of the team what direction to go.
I have SQL Reporting Services 2005 installed. When I go into the SQL Reporting Services Configuration Manager I immediately get an error that says that "No report servers were found on the specified machine" then Details: Invalid Namespace.
The machine that it is running on is our report server. The name in the Machine Name says the name of the report server not the name of the specific machine so maybe this is the problem? I think the name of the report server should go in instance? Problem is that it won't let me change anything and it just gives me that error and everything else on the screen is disabled.
Hey guys, got a question regarding hardware configuration for a new SQL server we are installing here. We are migrating from a Windows 2000 standard, SQL 2000 standard box with 4GB of RAM to a Windows 2000 datacenter, SQL 2000 enterprise box with 32GB of RAM.
What would be the best way to set this up in Enterprise Manager? Should I just let SQL Dynamically use the RAM or should I select a fixed amount? Also with the processors, should I allow SQL to use all available processors or just 3 of the processors? Also with such a large amount of RAM, I've read people setting there pagefile to 0 so there is no load on the hard drives.
We are running SQL 7 on a server, and are moving to a new server and will beupgrading to SQL 2005 at the same time. Currently, both the old and the newservers have two drives, one for programs and one for data.With the current configuration, SQL 7 and the data are both installed on thedata drive, in the MSSQL7 directory. Our sys admin wants to install SQL 2005on the program drive of the new server, while putting our databases on thedata drive. I argue that if SQL itself is on the program drive, then thesystem databases will be in one place, while our databases will be inanother. So I'd prefer to have SQL 2005 installed on the data drive, as itis now.I'm interested in any feedback regarding what you guys think is the betterconfiguration, and also if there's any performance hit from having theprogram and the database on two drives of the same machine.Thanks,Neil
In my Package Configurations, I have three different configurations. Two are of the type, SQL Server and one of type XML. I want to get the currently used Configuration name, Configuration String and the configuration type in my Script Task code. Can anybody help me with this code. Thank you.
We store our configurations in a SQL Server configuration table. We have SSIS packages that can be used in different senarios. Each of these senarios requires different configuration values.
Is it possible to change the SSIS configuration key used for a package at run time? i.e. we'd like to be able to use a job that says run this SSIS package but use configuration values with that name.
I've installed SBS 2003 with SQL 2005. The MBSA shows that the SQL service and agent should not be run as a Local System or Administrator. I've tried to create a new user and start the service with the new user, but no matter what I do, I cannot start the service with the new user. I've tried to add the user to the SQL* groups and Domain Users group, but no luck. I've tried adding permissions to those groups to all the SQL directories, but again, no luck. I'm getting error 5 - permission denied. Anyone got a good HOWTO?
Hi, I am very new to SSIS and SSRS.I just installed in sql server 2005 professional in my office machine. I am going to work on SSIS and SSRS. My machine itself will be the server.Could you please tell me what are the configurations I should do?.. Thanks in advance. -Selva
In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.
What will be the best way to configure the same? and Please let me knowwhy that is the best choice.
Hi All, Using Visual studio 2005 you can create a simple database to enable the use of login using asp.net configuration by clicking on website on the menu bar. By default the database is created and stored in the App_data file. However my service provider has a separate server for SQL (SQL Server), therefor it will not work. How can I use this Asp.Net Configuration to create a database on my sql server? Hope this question makes sense!!
Hi Guys Got this problem, ive setup some new roles and users through asp.net configuration and it creates ASPNETDB.MDF in the App_Data folder when ive got a DB in SQL server 2005 express. Basically how do i change it so the tables created for the users and roles are created in the SQL Server 2005 Db? p.s I'm very new to sql server 2005 Thanks
To get an understanding of the SQL Server technology and the hardware requiremnets. To configure a VLDB system for OLTP what is a typical hardware configuration that will be used.
No of Rows:10 Million No of Tables : 50
Will have to spilt this out into many databases, or will one database be able to handle this. Assume that the database is normalized.
Any suggestion or information will be of great help. Does MS have any information relating to this information on their web site?
Hi, I have to buy a new machine for SQL 7.0/NT. I know the db size it's going to have. How can I get what machine configuration I should suggest. I mean...what should be the RAM/HDD size/etc..? We are expecting around 40hits/day? Anyone have any idea? Please suggest. Thanking you.!
It might be a silly question but really took my head. I have a http server and wanted to install SQL server there, so that my client in US can access it. I just wanna know is there anything specific settings while or after installation I need to do so that they can access it and can configure in their Enterprise Manager. Is it okay if I simply install SQL SErver 2000 in my http server and give the IP address of the server with the port number so that they can go to Client Network Utility and add my server. Later they can come to Enterprise Manager and connect to that?
I am about to rebuild the primary SQL Server 6.5 server at our site. i would like to hear from those experienced in this regarding the optimal configuration settings. I have Microsoft`s recommendations, but want to hear from some real-world experiences. I have 1GB of RAM and a 27 GB hard drive.
We are upgraded from sql 7.0 to sql 2000. We have a brand new server in which we are installing it on. We are trying to decide on the best setup for it. The db are a datawarehouse. So mainly used for input. Gets updated with new data once a day.
We were going to have: Partician 1 : Raid 1 - OS and Applications
Now we are trying to decide on the other particians: We have 6 36 Gig Drives to work with. One of them is going to be a hot swappable So basically we have 5 36 Gig drives to work with. I've read that its better to divide up the data/logs/tempdb. Would the following setup be good for performance or if not what would you recommend: We were thinking of making all of them Raid 5. My network guys say that with Raid 5 I'll end up with about 70 % so that accounts to about 127 Gig. So I was thinking the following:
I have to build NT server for a SQL database which is just over 1.1 Gb. Does anybody have any suggestions for server configuration that I should look into i.e. Splitting the data and trans log across different disks. Amount of memory .... etc.
