I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.
Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.
I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.
So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?
We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck. The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?
John
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'affinity mask', N'0'
GO
EXEC sys.sp_configure N'affinity I/O mask', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
-----------------------------------------
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity mask' does not exist, or it may be an advanced option.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v," "master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 " "and v.number = r.config and v.name ='affinity mask'");
I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.
However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'
A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.
I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg
"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"
It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.
The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.
Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.
Hi everyone, Primary platform is 2005 under 64-bit.
I'm trying to figure out what does 'Processor Affinity' and 'I/O Affinity' means when you're viewing server properties. We've got eight processors. Thanks for your comments,
I have recently upgraded my databases from SQL 2000 to 2005. In 2000, I could set the network affinity so that sql server was only accessible from one of the two network cards. I cannot find how to do this in sql 2005. Is there a way?
In my installation, I have two network cards in each server. The "A" network adapter connects to the "A" hub, and the "B" adapter connected to the "B" hub. Traffic on trhe "B" hub is my private, non-routable network, This is where I want the SQL Server traffic to run. The A hub traffic is the public network. This gives me the ability to isolate SQL Server from the outside world.
I'm trying to set affinity to mssearch.exe in our Windows 2000 Advanced server (4cpus), but i am getting an "Access denied error". I checked the login which has administrator rights to the box.
It would be a great help if anyone can tell how to fix this.
We are currently looking at consolidating 10 servers into one cluster server.
Some servers may be busier than others. Is there any reason to split them up and give the busy databases specific CPUs or is it always better to have them on one instance?
-- Prepare sample data DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))
INSERT@Sample SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL SELECT3, '172.16.11.0', '255.255.255.252'
-- Show the expected output SELECTID, NetworkIP, SubnetMask, dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP ( @IP VARCHAR(15), @NetIP VARCHAR(15), @MaskIP VARCHAR(15) ) RETURNS BIT AS BEGIN RETURNCASE WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) THEN1 ELSE0 END ENDE 12°55'05.25" N 56°04'39.16"
I'm using the ForEach File task and masking the files I want to process as ABC??.TXT. It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?
sorry for my question, maybe it seems un professional but I need to know the answer,
is it possible to enter the data in a sql table in a specific format and how it could be?
for example I want to have a mask in the table to enter the data in this format ##.## so it will not accept any other data to be typed in without this format.
Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.
I have statement which is comparing a smalldatetime column to literal string as follows:
sales_date ='21-9-2004 0:0:0.000'
when I run the statement in query analyzer it bombs out with:
Server: Msg 296, Level 16, State 3, Line 1 The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.
Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?
AnupG writes "hi, the most common feature that any RDBMS provides should be that if we want to store the data in the MSSQL tables then the data should be presented in masked/formatted like for eg.the password text in field should be like "*****". I am using SQL 2000 but i unofrtunately i couldn't find any such feature in defining the data type in SQL server 2000 ...?"
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]
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.
