Hi all. Dorky question , but I am still relatively new to the world of
ms database servers so bare with me. I am monitoring the page fault
rate on a server and it runs at 100% almost all of the time. Can
someone help me understand what that means?
I am running an application on one NT Server, running against SQL Server 6.5 sp 3, and SQL 7 with sp1 applied.
The application is a 'data migration' type application - ie heavy insert and update workload - against many (50+ tables) with many different SQL statements.
The SQL 7 server is configured with 'floating' memory.
On SQL 7 - I am experiencing very high page faults/second for the sqlservr process - sometimes peaking at over 1,000. I was under the impression any number greater than 10 indicates a problem with system performance.
The same application, same data, same NT configuration etc against SQL 6.5 does not page fault. SQL Server 6.5 completes the work faster than 7.
Could anyone help me understand what's going on ?
Thanks in advance.
My question is, My perfmon counters reads anywhere between 0 and 1200 with the average being around 250 faults/second.
My concern is my memory max size being too large, I have 4gb ram, SQL reports usage at 2.9 GB, my max mem size is 3.9 gb, should I maybe set my SQL server to use a fixed memory size of 2.9 gb?
Thanks in advance
Pete Karhatsu
Copied from SWYNKs' article:
Process: Page Faults/sec
If this value is greater then 0 then the SQL Server process is producing soft page faults and as a result CPU overhead. Try setting the working set size value to be as close to the SQL Server's memory allocation.
Hi, all.We have a couple of pathological sql servers that have lots and lots ofpage faults per second, up to 4000. Our client programs are written inC#/.NET 1.1 and utilizes connection pooling.Some of the client programs seems to log in hundred of times persecond, as reported by perfmon->.SQLServer:GeneralStatistics->Logins/sec. Stopping the client programs reduces thatnumber significantly.We've done code reviews of the client programs and they look OK.Monitoring .NET connections&pools does not show anything suspicicous.We're currently rewriting the clients to use one db connection insteadof the pools, but that takes some time and may introduce bugs. Doesanyone know why we have these problems and/or why logins/sec is sohigh? I'm thinking "bugs in the .NET client", but really have noidea...One thought I had was that the Page Faults reported for sqlsrv.exe isrelated to memory mapped IO and therefore can be ignored. Right orwrong?Any thoughs/pointers/ideas, even wild guesses, are most welcome.BjørnPS: The server memory is fixed at 1.5GB out of 2GB physical ram,clients run on the same machine and use TCP/IP comm.(I know...) Thehost itself is not paging.
View 4 Replies View RelatedWe are running SQL Server 2000 Enterprise Edition on a 2-node cluster with IIS/ASP.NET front-end hosting 150-200 active connections. There is a SVCHOST process running under LOCAL SERVICE account - hosting the Remote Registry process that is using only 4,200K but is page faulting 200-500 times per second. I realize this process is used for failover, but the page fault seems excessive. Any thoughts on this?
The servers are running Windows Server 2003 with 4 processors and 4gb RAM.
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
What is lock page memory. How it will work and why it will increase the performance.then what is the difference between setting the - max/Min server memory and Lock page memory
View 1 Replies View RelatedHi,When i eg. manually ad entries to a table and, cancels the insert Ms SQLincrement the counter on the ID anyway. Is there a way to avoid thisbehavior?RegardsAnders
View 1 Replies View RelatedI have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedHello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.Â
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies View Related<usualDisclaimer>Please forgive me if this is in the wrong group, and if so,what is the right group.</usualDisclaimer>Let me start off by first saying im a newb. Ok, with that out of the way Iam trying really hard and boy have I learned a lot in the last little whilebut I have a question i just can't seem to find a good answer to.Lets say i have a table that simply stores how many times someone has loggedinto a webpage. Is it better to store each login as a new record then countthe records or is it better to simply have one record that updates the totalvalue by incrementing that one field. I have read all manner of articlesand some say one way is better vs. the other but what I can't find is why?If I knew why one way was better than another then I could make and educateddecision and choose the best way that is right for me. Is updating more orless expensive then inserting? Does it matter and is a relevant question?And before anyone comments on my use of all uppercase letters for my tablename I do this so that my table names stand out within the sql serverenterprise manager. In other words system tables are lowercase and mytables are uppercase. People always seem to give me crap for this but neverback it up with a good explanation so far as I can tell its personalpreference? Am I wrong?CREATE TABLE USER_METRICS(-- here i can select all the records and count them up.ID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250));-- Or this?CREATE TABLE USER_METRICS(-- and of course here can i simply retrieve the value of Total_LoginsID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250) NOT NULL,Total_Logins INT);Regards,Muhd
View 3 Replies View RelatedHello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
I am running Visual Studio 2005. I have an SSIS Package which is consuming a huge amount of memory. During the execution of the package the memory keeps increasing. Until finally i get an Out of Memory exception. I have run this package using dtexec, and in the BIDS. No difference. I do have some script components and have added some code to get the assemblies in the current appdomain. I do see that one particular assembly is increasing on every loop. VBAssembly every time it hits the script component is increasing by 6, and along with it the memory is climbing. What is this VBAssembly being used for is there an update to SQL Server Integration Services that I need?
Thanks! Aaron B.
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
I've a database with a memory optimized filegroup on it. How can I remove it?I have removed the memory optimized table I had on it, but when I try to remove the filegroup I receive an error.
View 12 Replies View RelatedHello DBA's
i use this proc to insert & update the records in the databse
update works good untill i change the user id.
userid can be changed where adminuserid is the IDENTITY coulmn in the table.ALTER PROCEDURE [dbo].[spinsertusers]
-- Add the parameters for the stored procedure here
@adminuserid varchar(36),
@userid varchar(15),
@fname varchar(25),
@mname varchar(25),
@lname varchar(25),
@password varchar(15),
@address1 varchar(255),
@address2 varchar(255),
@postcode varchar(15),
@cityidentity varchar(36),
@dob smalldatetime ,
@email varchar(50),
@crtduser varchar(36),
@crtdon datetime,
@isactive char(4),
@mode char(10),
@reccount INT output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Count INT
-- Insert statements for procedure here
IF (@mode='insert')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @count=0
--Inserting the Records
BEGIN
INSERT INTO [school].[dbo].[AdminUserMaster]
([AdminUserIdentity]
,[FirstName]
,[MiddleName]
,[LastName]
,[UserID]
,[Password]
,[Address1]
,[Address2]
,[PostCode]
,[CityIdentity]
,[DOB]
,
,[CreatedBy]
,[CreatedOn]
,[IsActive])
VALUES
(
@adminuserid ,
@fname ,
@mname ,
@lname ,
@userid ,
@password,
@address1,
@address2,
@postcode,
@cityidentity ,
@dob,
@email,
@crtduser,
@crtdon ,
@isactive
)
SET @reccount=2
return @reccount
END
-- End of Query
IF (@mode='update')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid)
IF @Count=1
--Query for Update the Records
BEGIN
update AdminUserMaster
set
[FirstName]=@fname ,
[MiddleName] =@mname,
[LastName]=@lname,
[userid]=@userid ,
[Address1]=@address1,
[Address2]=@address2,
[PostCode]=@postcode,
[CityIdentity]=@cityidentity,
[DOB]=@dob,
=@email,
ModifiedBy=@crtduser ,
ModifiedOn=@crtdon ,
isactive='Y'
where
AdminUserIdentity= @adminuserid
set @reccount=3
return @reccount
END
-- End of Query
END
]First of all here is how i created my SQL database and established a connection in visual studio 05. I go into the server explorer and right click on the data connections node and and select "create new SQL database". A window then prompts me for a server name and data base name. Under server name I write "localhostSQLEXPRESS" and under data base name I put in whatever. This totally worked and the database was asigned the extension of .DBO
I can now insert, select and delete information with some simple ADO coding. The only problem is that using this method makes it seamingly impossible to FTP my database onto my host server. I am aware that I can create a data base in the APP folder by simply right clicking it and selecting " new database".
This would simplify the deploying process but I cant seem to establish a connection this way. Ive also noticed that when I use this method that it saves it as a .MDF instead of a .DBO. What is the difference? Why does my .DBO work but not my .MDF in my app folder? Im so confused over this issue that I am problably not even asking the right question. Bottom line is that I have a small web application with a .dbo database that works wonderfully in my isolated visual studio environment and I need a solution to deploy it on the world wide web. Please help with any information I would be very thankfull.
Between having SQL and using SQL server enterprise manager with MSDE
View 1 Replies View RelatedI am not an expert with sp. I am trying to pass the column name as a parameter but I get the following error "Must declare the variable '@strColumnName'."
ALTER PROCEDURE dbo.TempGetDataForDropDown
(
@strColumnNamenvarchar (50)
)
as
EXEC ('SELECT DISTINCT @strColumnName = ' + @strColumnName + ' FROM Customers')
SELECT 'http=' replace (prot,'ww','+')',+'replace(city,'u','+')',+'proj_s tate'+'proj_zip5
INTO :ls_MAP FROM project4 WHERE proj_id=:gv_proj_id;
Hi all,
I've recently created a shopping cart system that uses SQL Server on the backend. The shopping cart will add a record to a table called CartEvents each time anything happens, like view item, update, delete etc. anyhow, the site has been up for about a week now and that particular table has about 4200 records already, I project tens of thousands of records in that table, so how many records is too many records? Whats your experience with tables that have hundreds of thousands of records? How many records can SQL server handle and still perform well?
Thanks
What is ALL,SOME|ANY ? Whats the usage?
Is they are comparison operators?
If not then what kind?
If SOME and ANY are same then where to use SOME and where to use ANY?
Im using windows xp sp2 and MS Sql server 2005 enterprise edition may b and im also havin sql server express
previously when i installed sql server express it took server name on its own and i used to run queries using AdventureWorks database..
Recently i formated my desktop and again installed both the versions im having but neither is taking server name.. and i dint noticed what was the server name before.. im a student home user i just hav to query on adventurework databases on the same computer i cant afford to buy any servers..
so can anyone tell me how can i configure sql so that i can query !
DECLARE @DBName varchar(100);
SET @DBName = 'TestTest';
CREATE DATABASE @DBName ON PRIMARY;
message is: wrong syntax near '@DBName'.
I've been writing a few sql queries over the past few days, and would like to know what the difference is between:
website is null
website like ''
Becasue if they mean different things i want to know how to look them both up at the same time. Obv im after data that is blank or has nothing written in it.
I am trying to run an sql query with a function in SQL SERVER MANAGEMENT STUDIO and am getting the following error.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'UserID'.
here is my Query
-----------------
USE vportal4vsearch
GO
CREATE FUNCTION dbo.ConcatUserGroups(@UserID int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @groupname Varchar(2000)
SELECT @groupname = COALESCE(u.group_name + ', ', '') + CONVERT(varchar(20), u.group_name)
FROMdbo.[user] A
JOIN dbo.UserGroup_combo UG
ON UG.ug_userID = a.USER_ID
JOIN dbo.usergroup U
ON UG.ug_groupID = u.group_ID
WHEREA.USER_ID = @UserID
ORDER BY U.GROUP_NAME
RETURN @groupname
END
GO
SELECT
user_ID,
user_Firstname,
user_Lastname,
user_Email,
Username = (Case When user_Username = ' ' or user_username is Null then 'None' else User_Username end),
user_creationdate,
Active = (Case When user_Active = 1 then 'Yes' else 'No' end),
dbo.ConcatUserGroups(UserID),
TheatreCode = (Case When User_TheaterCode = ' ' or User_TheaterCode is Null then 'N/A' else User_TheaterCode end),
JobTitle = (Case When user_JobTitle = ' ' or User_Jobtitle is Null then 'N/A' else User_Jobtitle end)
FROM [user]
GO
DROP FUNCTION dbo.ConcatUserGroups
GO
[CODE]
Private Sub pulllikehell()
Dim cn As SqlCeConnection
Dim rda As SqlCeRemoteDataAccess = Nothing
Dim sqlEngine As SqlCeEngine
Try
' Create database if it doesn't already exist
If (Not File.Exists("My DocumentsORDER.sdf")) Then
sqlEngine = New SqlCeEngine
sqlEngine.LocalConnectionString = "Data Source=My DocumentsORDER.sdf;"
sqlEngine.CreateDatabase()
sqlEngine.Dispose()
Else
' Open the connection to the database
cn = New SqlCeConnection("Data Source=My Documents" & _
"ORDER.sdf:")
cn.Open()
Dim cmd As SqlCeCommand = cn.CreateCommand()
' Drop the FieldMemos table
cmd.CommandText = "DROP TABLE STAFF"
cmd.ExecuteNonQuery()
' Close the connection
End If
' Instantiate the RDA Object
rda = New SqlCeRemoteDataAccess
' Connection String to the SQL Server.
Dim remoteConnectString As String = "Provider=SQLOLEDB;" & _
"Data Source=KANGALERT/SQLEXPRESS;" & _
"Initial Catalog=ORDER;"
rda.InternetLogin = ""
rda.InternetPassword = ""
rda.InternetUrl = "http://kangalert/server/sqlcesa30.dll"
rda.LocalConnectionString = "Data Source=My Documents" & _
"ORDER.sdf;"
rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
RdaTrackOption.TrackingOnWithIndexes)
MsgBox("ok")
Catch sqlex As SqlCeException
Dim sqlError As SqlCeError
For Each sqlError In sqlex.Errors
MessageBox.Show(sqlError.Message)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
rda.Dispose()
End Try
End Sub
[/CODE]
computer server name is kangalert
and got 2 sqlservice run in my pc, SQLEXPRESS and MSSQLSERVER
DATABASE NAME IS ORDER with a TABLE call STAFF
when come to this statement
rda.LocalConnectionString = "Data Source=My DocumentsORDER.sdf"
ERROR MESSAGE: A request to send data to the computer running IIS has failed. For more information, see HRESULT.
when come to this statement
rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _
RdaTrackOption.TrackingOnWithIndexes)
ERROR MESSAGE :A required property is not specified. [ Property name = SubscriberConnectionString ]
HOPEFULLY ANYONE CAN HELP ME SOLVE IT. THANKS IN ADVANCE
Whats wrong with my stored procedure i thought i am declaring the varible when i write this check it out can some one help me out thanks!!!!
CREATE PROCEDURE dbo.GetCompanyDEMO
[
@CompanyName nvarchar(40)
]
As
Select ContactName, ContactTitle, Address, City, Region, Phone
FROM Customers C
Where C.CompanyName = @CompanyName
some one tell me what is trigger and whats the use of it? and when do i use it?
some one show me demo , example of trigger
thank u
maxs