MS SQL 2005, .NET, Logins/sec And Page Faults
Jul 25, 2006
Hi, all.
We have a couple of pathological sql servers that have lots and lots of
page faults per second, up to 4000. Our client programs are written in
C#/.NET 1.1 and utilizes connection pooling.
Some of the client programs seems to log in hundred of times per
second, as reported by perfmon->.SQLServer:General
Statistics->Logins/sec. Stopping the client programs reduces that
number 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 instead
of the pools, but that takes some time and may introduce bugs. Does
anyone know why we have these problems and/or why logins/sec is so
high? I'm thinking "bugs in the .NET client", but really have no
idea...
One thought I had was that the Page Faults reported for sqlsrv.exe is
related to memory mapped IO and therefore can be ignored. Right or
wrong?
Any thoughs/pointers/ideas, even wild guesses, are most welcome.
Bjørn
PS: 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...) The
host itself is not paging.
View 4 Replies
ADVERTISEMENT
Aug 11, 1999
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.
View 2 Replies
View Related
Jun 13, 2000
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.
View 1 Replies
View Related
Jul 20, 2005
Hi all. Dorky question , but I am still relatively new to the world ofms database servers so bare with me. I am monitoring the page faultrate on a server and it runs at 100% almost all of the time. Cansomeone help me understand what that means?
View 1 Replies
View Related
Jul 31, 2007
We 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.
View 1 Replies
View Related
Jan 21, 2007
ok, first, I know... I forgot to run a backup of the master database, and I forgot to run a script to caputure logins. Not that that is out of the way... I need to recreate the logins under the Securities tab below the databases. All the company databases have the user names and passwords assigned to them, but they are not able to login, because they are not able to authenticate to the SQL server first.
Is there a script that someone has that will copy the company database security info for the users and recreate them in the SQL security tab?
I know that I can rebuild them manually, but I need to delete them first in the application software, then delete them from the databases, and then recreate them in the application software... and as simple as that sounds... it is a slow moving process.
Any assistance would be greatly appreciated.
Thanks,
John
View 3 Replies
View Related
Jul 20, 2005
Hi,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 Related
Sep 18, 2006
I need to create a new login that only has access to 2 Views.
I have tried everything, A New Role, Schema, set the Securables but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can still a bunch of tables. I only want this Login to be able to run either view and not see anything thing else.
View 13 Replies
View Related
Feb 8, 2007
What is the relationship between the users in a database and the logins on the server.
View 1 Replies
View Related
Jun 22, 2007
As from what i see there are different system files for logins.
Is there a way to transfer SQL 2000 logins to SQL 2005 i am used to the
sp_help_revlogin i think thats the script..in 2000
Is there a way to convert all logins from SQL 2000 to SQL 2005
View 3 Replies
View Related
Feb 5, 2008
Hi all,
I am using SQL 2005 (Enterprise Edition, SP2) Mirroring with a Principal, Mirror and Witness set to High Safety mode and am a little confused!
As far as I understand it, during a failover, although the database content will automatically be available on the Mirror any user accounts will need to be either manually created or manually mapped to the new principal. Is this the case? If so, I can't really see the purpose of using Mirroring (in my scenario anyway).
For example - We principally use the database servers for our web applications which we connect to using ADO.NET. Each database and Web app has a different SQL user account to allow access etc. If we experience a failover this means the users will not be able to use the web app until we have manually recreated/mapped the SQL accounts for each and every database. Surely this is a massive oversight?! Or am I simply getting the wrong end of the stick.
Any help/guidance would be greatly appreciated!
Thanks,
Richard
View 3 Replies
View Related
Jan 10, 2007
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
View 2 Replies
View Related
Mar 17, 2008
I was wondering if anyone could help me with this. I am currently working on a project for reporting on a new acquisition the company have made. Unfortunately, the people I have to give access to the SQL Server are using Netware for authentication.
Is there any way to allow these people to log into SQL Server without having to create a SQL authenticated user for each of them?
Thanks
View 7 Replies
View Related
Aug 3, 2007
I backup SQL 2000 DB and Restored to SQL 2005
Under the Database Name, Users when i click properties the login name is blank.
When i restored from SQL 2000 to another SQL 2000 Server i would run an orphan fix
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for
select name as UserName from sys.sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name
for read only
open Crs
fetch next from Crs into @usrname
while @@fetch_status=0
begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end
close Crs
deallocate Crs
I changed the sys.sysusers from sysusers.
But when i run this it does not work.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Does anyone know how to fix the logins for all of the users rather than one at a time for SQL 2005
Thanks
View 9 Replies
View Related
Mar 8, 2006
Hello,
We use one standard account with a password that is never given out.
In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.
That same process does not work for SQL 2005.
Below is a sample of the script that we were deploying for SQL 2000 installations.
-------------------------------------------------------------------------------------------------------------
-- Login: CPAPP
declare @pwd varchar(50);
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @pwd, @defdb = 'CPSQL' , @sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @pwd,@defdb = 'CPSQL' , @sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @encryptopt = 'skip_encryption'
SET @pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @pwd, @defdb = 'CPSQL' , @sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @encryptopt = 'skip_encryption'
-------------------------------------------------------------------------------------------------------------
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Is this what we should be using for SQL 2005?
Please advise.
Thanks in advance
View 6 Replies
View Related
Jul 17, 2001
Hello,
I would move a Database to another server. I try to use DTS but I have problems with this process because DB have big tables, I think. I try to use DETACH and ATTACH procedures but logins doesn't export. And more, in new server there are already logins from another DBs.
What's the best way to solve this problem?
Please, help
Thanks
View 3 Replies
View Related
Apr 3, 2007
I am a systems analyst and work with an app that runs against 2 SQL Server DBs. Though I have some familiarity with SQL Server and SQL, I am not a DBA.
The app executable is tied to a Windows service.
When we install the app, we run a process that builds 2 dbs to include:
Tables, indexes, stored procedures, views and user accounts.
SQL Server is set up for mixed mode authentication.
Normally, the dbs run off the local db user accounts which are tied to local logins with the same names.
We have a client that wants to remove our standard logins so that they can run on only a Windows login.
I know I should be able to tie the db users to a Windows login.
And I can do the same for the service.
But I am at a loss as to how to get this done.
How do you associate db users with a Windows login?
When I have tried sp_change_users_login I get an error that the Windows login does not exist. (Though I have added the Windows account to the DB.)
Hope this all makes sense.
View 2 Replies
View Related
May 13, 2008
Hi
How to transfer logins from 2000 to 2005?
Raj.
View 1 Replies
View Related
Jul 21, 2006
I do not understand the sequence in which to add logins/users. A coupleof things to note. I can not use vbscript - it has to be done using thesqldmo objects (or in a sql script).The sequence I use now is:1- create login, set default db2- add login to sql server3- set db owner4- create user5- add user to master db6- add user to tempdb - blows up hereError 'Microsoft SQL-DMO: [SQL-DMO]This object is already in acollection.'I need the user in 3 dbs. Any help greatly appreciated. Thinking aboutit...I bet by adding it to the master db that it is automatically addedto tempdb so I will try to add it to the 2rd db and see if that works.Thanks!-Will
View 2 Replies
View Related
Mar 30, 2006
Right im getting slightly annoyed LOL
We have an MSSQL 2005 box in the shared hosting environment now the amount of attacks we receive every hour is a crazy amount. The usual stuff as below
Login failed for user 'sa'. [CLIENT: ]
Now i can simply block the ip's in ipsec but the amount of attacks is crazy and its taking all my time to keep track of them all, i know its just websites, automated tools etc, mssql is set to lock accounts after 4 invalid logins, well to make security sense i renamed the sa account as its default on most servers.
What i want to know is after so many attacks why can't mssql implement ipsec security on its own and block the client ip, come on after 20 attempts you know the IP is brute forcing the accoutn in question.
Any advice people?
Its a high performance server but it doesnt need brute force attacks against it all day long, LOL.
View 3 Replies
View Related
Jul 27, 2006
Hey. I've a script which gets the SQL usernames with the information I need. Is it possible to migrate the passwords also with this information?
Also, regarding Windows Logins, do i have to worry about their SIDS when migrating to SQL 2005. I'm going to attach SQL 2000 db's onto a 2005SQL instance and these db's alreayd have the usernames assigned in them. Please let me know if there is a way out for the above two questions. thank you.
SELECT 'Create LOGIN [' + loginname + '] with password = ''hello'', sid = ',sid,
', default_database = ' + dbname + ', default_language = ' + language + ',check_expiration=off,check_policy=off'
FROM master..syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
View 6 Replies
View Related
Dec 19, 2006
what is maximum no. of concurrent Logins Possible on a SQL server 2005 Database?
View 3 Replies
View Related
Jul 6, 2007
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
View 5 Replies
View Related
Nov 11, 2015
I 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 Related
Nov 28, 2006
Hello 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€¦
View 2 Replies
View Related
Dec 4, 2006
Hi guys,
I have tried to use the sp_help_revlogin script to move logins from a sql 2000 server to a sql 2005 server. It works for users that have a password. But if the password is blank it will fail. Is there a work around for this since I can't change the users passwords?
I have also tried SSIS package but that too doesn't work because of the same reason.
Can anyone help...
View 1 Replies
View Related
Jan 31, 2007
Very simply, I need to add a cover page to my SSRS 2005 report. I've tried this using the Page Header region, but the cover page will not show up on Preview or Export. How and where do I create a cover page?
View 1 Replies
View Related
Aug 9, 2007
Hi,
I have used Report viewer control to show ssrs 2005 report in my aspx page.
Can we show the SSRS 2005 report on the aspx page in any other way?
any info on this is appreciated.
Thanks,
Srik
View 28 Replies
View Related
Oct 30, 2007
Hi,
i am working on SSRS 2005. i have one problem while accessing report throgh ASP.net pages.
i have used multivalue parameters in the report. on the report server it works fine but on the .asp page when that report is accessed, drop down is not working,
can anybody guide me regarding this , what to do?
thanks and Regards,
Vishruti.
View 1 Replies
View Related
Nov 29, 2007
I currently have a login page in asp.net 2.0 linked to a SQL 2005 database table that holds the usernames and passwords. At present, I am on an "honor system" where I do have access to the passwords of the other users but would like to change it so that I cannot know what the users' passwords are. Thank goodness that there is no personal information within the pages and the logins were created to keep a log of who logs in and what not. However, I would like to soon hold more personlized information, hence the need to encrypt each user's password even from myself.
I have read up on Symmetric Encryption for SQL 2005 but I would like to know if there is anything else available, any good proven methods that someone else has already tried.
Also, while testing out Symmetric Encryption, I noticed that I have to supply the encryption password for the decryption. However, if I know what the password for the encryption/decription is, does it not defeat the purpose of having the encryption at all, in terms of the "Admin" having access to sensitive information? Just curious if I understood the concept correctly or not.
Thanks in advance to all.
View 2 Replies
View Related
Jan 8, 2007
I have aspx page which uses Reportviewer object to execute/display reporting services report on the page based on the parameters passed by the aspx page.
It works fine. I have a problem when I try to use import option on that page. It throws the following error. Can anybody help me to resolve this issue?
Advanct thanks for your help and support
Thanks,
Sundar
Here is the error message when I export the report to any format (Excel, pdf, csv or text)
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Xml.XmlException: For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
Here is the code:
ASPX Code:
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="100%" ProcessingMode="Remote" Width="100%" ShowFindControls="false" ShowPageNavigationControls="true" ShowPromptAreaButton="false" AsyncRendering="false" EnableViewState="true" ShowParameterPrompts="false" Visible="false">
<ServerReport ReportServerUrl="http://miscsql1/ReportServer" ReportPath="/webreports/POS Commission Report"></ServerReport>
</rsweb:ReportViewer>
ASPX.VB Code
Dim RptParameters(2) As Microsoft.Reporting.WebForms.ReportParameter
RptParameters(0) = New Microsoft.Reporting.WebForms.ReportParameter("Repcode", objCommon.RepSite.ToString, False)
RptParameters(1) = New Microsoft.Reporting.WebForms.ReportParameter("StartDate", txtStartDate.Text.ToString)
RptParameters(2) = New Microsoft.Reporting.WebForms.ReportParameter("EndDate", txtEndDate.Text.ToString)
ReportViewer1.ServerReport.SetParameters(RptParameters)
ReportViewer1.ServerReport.Refresh()
ReportViewer1.Visible = True
objCommon = Nothing
View 5 Replies
View Related
Sep 17, 2007
Hi,
I'm having 2 issues on my first "real" SSRS report.
1. How to do a print preview via the preview tab. For print rendering, I have no print preview button on the preview toolbar. Is this normal? MS documention said that there is a Print Preview button. (BOL May 2007 - Designing & Creating reports -> working with report designer -> debugging and Publishing reports (Print Preview)) This report will always be rendered via the printer and it is a "pain" to have to always print it to see changes. The print layout option comes up all black with nothing viewable.
2. How do I reset page numbers back to 1 for report groups? I see no option for this. My report group requires a new page and I want page numbering to start at 1 when the grouping changes.
Note: I'm running VS2005 via Vista (with VS2005 Vista fix installed). SQL2005 SP2 also installed.
Any advice on this will be appreciated. I've spent too much time already where this should be obvious.
View 2 Replies
View Related
Dec 28, 2006
Creating a web application. Running a simple query "SELECT username FROM vwCustomer"
vwCustomer is a view built on top of an Access DB which is set up as a linked server. Within SQL Server that view responds immediately. But when I try to access it from an ASP page it takes over 20 seconds to respond.
Used the following to create the linked server:
EXEC sp_addlinkedserver 'DBName', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:databasesdatabase.mdb'
GO
EXEC sp_addlinkedsrvlogin 'DBName', FALSE, NULL, 'Admin', ''
GO
The view is simply(this works fine in SQL Server):
SELECT * FROM DBName.db.dbo.customer
The ASP page:
SELECT * FROM vwCustomer
Can anyone give me a hint as to where the bottleneck is? Or how I can figure that out using SQL Profiler or something?
Please help!
View 3 Replies
View Related
