SQL takes all CPU resource on some of the intensive
queries. Is any way to make sure there is something left for other tasks
to be processed? Let's say limit SQL to use no more than 80% of CPU.
Thanks
Hello,
I was wondering if there was a setting or a way to limit or restrict the amount of Memory an SSIS package can use? I know that by default the windows OS limits a process (a package in this case) to 2GB and up to 3GB with AWE enable but what if I wanted to say Limit it to 1GB of memory is there anyway to do that? Is there an SSIS Engine setting or Package property somewhere?
Thanks!
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
Hi there guys!
I was wondering if anyone know the number of rows you can import from an external source into MS SQL Server 2000 Developer Edition?
I have tried importing a whole table from another database with over a million records. But failed after 1.7 million row.
Any ideas?
Thank you!
When creating a database, SQL Server 6.5 seems to have a 2 gig limit. What I mean is that if the device chosen is over 2 gigs SQL Server displays the size of the device as anegative number. This prevents me from being able to expand the database when I need to. Can anyone tell me why this is so, and if there`s anyway around it??
View 1 Replies View Relatedwe all know
mysql: select * from table limit ?1, ?2
equals
sqlserver: SELECT TOP ?2 *
FROM table WHERE (IDENTITYCOL NOT IN
(SELECT TOP ?1 IDENTITYCOL
FROM table order by IDENTITYCOL))
order by IDENTITYCOL
but the below SQL in mysql,how to convert?I enmesh...........
select pageid,pagename,pageaddr,pageauditflag,pageartaudi tflag,startplaytime
from pageinfo where entryid= ?1 and startplaytime= ?2
limit ?3, ?4
thanks!
hi,I have a question.Maybe You know the equivalent to command LIMIT from MySQLI couldn`t find something like this in MS SQLPSI try to display 10 records begining form e.g. 4 sort by idsomething like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY id"in MySQLthanx,Urban
View 2 Replies View RelatedHi There
I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.
However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.
So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?
Thanx
hi there,
i need to transfer the data off a query output to a txt file in the same order as it prints. i have tried using the command
eg.
exec bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword
this is exactly what i used replacing the appropriate parameters, but it gave me a "syntax error near queryout"
PLEASE advice what is wrong or sugest the best method to achieve the same.
Any help would be appretiated.
Thanks,
c: )
I need to provide some infomation on how much (trans/request)SQL7 can handle.
I checked the white papers and testimonials, but don't see any actual numbers.
We have a clustered SQL7 environment sitting on some Compaq 6400 using 4 cpu's. Our database size is only about 3.5 gig and we are using IIS 3. Does anyone know where to get this information?
Please advise
Thanks
Susan
Hi,
We have a SQL Server 2000 Enterprise Edition running as production
The CPU Usage is High when there were >350 user connections.
The buffer hit ratio is good. I checked all the monitor.
Can anyone can guide me why it is causing.
Thanks in advance,
Anu
Hi guys, I have a sell server its a new server 4 processors and 4 gigs of ram. SQL server is pinning the CPU's 100% I can’t figure out why, I'm at mdac 2.7 with the hotfix. I don’t know what else to look at.
Any ideas?
Hi .... I'd like to know if I can use BCP to transfer the whole database to a device or file and then back to the server(I mean,using only one server) with a new char set/sort order configuration,or I have to do it table by table??? How do I use BCP to perform this task?What commands and parameters should I use???
Thanks for your attention,
Luciano
we have CPU Usage upto almost 100% i increased the tempdb, and master to almost double the size
then we increased the memory to high amount.
does anyone have suggestion pls help since it running too slow, since we did an upgrade
I have a SQL 2000 Server, dual processer with hyperthreading. 2Gb of Ram. The machine is only being used as a SQL Server
Normally the server runs at about 15-20% usage. I have now noticed a problem where the SQL server suddenly jumps to 100%, this can happen after a week or a couple of days. It requires a server reboot to fix the problem. Stopping the SQL server and restarting will not work.
I don't know if this is part of the problem, but I have noticed that after a day or so the processor usage climbs by about 10%, if I stop the SQL service and restart, the processor usage drop by the 10%.
Also I have set the memory usage to be 1.5Gb, but it takes a day or two for the SQL server to consume this amount of memory. Don't know if this has anything to do with it.
Anyone know why this is happening ?
Kev
Hi All.
I am new to MSSQL, Iam using it to store largeamounts of data on a daily basis,that I import from a CSV file at the rate that I am going it should be about 1Gig a month of data. I noticed that as I add data to MSSQL my ram usage climbs by the size of the data. Is there someting Ihave done wrong inthe setup.
Thanks
Scott
I've got a copy of SQL2005 and IIS on the same machine. Sql Server seems to be eating a lot of RAM, eventually causing IIS to restart.
Anybody had any luck playing with max server memory ?
hi all,
when I click web page (executing some stored procedure generally will take less than 2 minutes) the CPU usage is becoming 100% and taking a long time to run.I already posted a forum before (SQL server 2005 running slow ). I dont know these problems are related.If I restart the server then it will run as ususal.What should be problem.Server Windows 2003 ,SQL server 2005
Is it bcos of any memory lekage or any other reason..
Thanks
leo
HiI am having a real issue with CPU usage by SQL Server, and it is notrelated to a poor query.I have a clients database that I am currently investigating some issueswith. After I perform a standard task using the application, and theresults have been returned to the application the cpu usage remains at100%.Even once the application has been completely closed down the cpu usageremains at 100%. Nothing else is happening.I am at a complete loss as to how to proceed to with investigation ofthis issue (i have been looking at this for over a week using SQLServer tools and Performance Monitor, and eliminating various otherpossibilities)I downloaded Process Explorer, and looking at the threads forsqlservr.exe there is one in particular that is consuming all of thecpu time:MSVCRT.DLLI am running SQL Server 2000 SP4 on the following machine:Windows 2000 SP4Pentium 4 3Ghz (Note this is seen as 2 processors so the reported cpuusage is 50%)1Gb MemoryI also have about 20Gig of free disk space.One other thing, the page faults reported on the Performance tab ofProcess Explorer exceeds 3 million. This is after running theapplication for around 10 minutes.Please can anybody suggest anything at all that might help? I'm sorrythere is not too much information in here but I have not been able tofind out anything useful!Many Thanks in advance.Paul
View 22 Replies View RelatedHi guys,Got an odd SQL string that I need to produce that is most probably simple toconstruct but with it being hot in our office, I simply can't get my headaround it....!!Its based around an online emailing facility whereby multiple hotels can beemailed via a single application. Users within the application have accessrights to email only specific hotels.The tables are laid out like this (irrelevant columns left out)...CampaignID, CampaignName, CampaignHotelIDs1 Test Campaign 1,4,5,7,92 Test Campaign2 1,2UserID, UserName, UserHotelIDAccess1 Test User 1,6,72 Test User 2,7Now on the stats page I want to give users access to view ONLY sentcampaigns to which they have access to view, I was considering the IN SQLstatement to achieve something like this...'WHERE CampaignHotelIDs IN UserHotelIDAcess'....but that doesn't want to work, can anyone give me any ideas to get thisworking within just a single SQL query?Cheers, @sh
View 7 Replies View RelatedHi,
I have 2 SQL Server 2005 instances in one server. We noticed that CPU usage is high in the server. Is there is any posibility to know how many percentage each SQL Server instances is taking?
LIke Total usage is 60%.
Instance 1 is 40%
Instance 2 is 20%
regards
Wilson Thomas
Hi all,
I want to write a backup application for SQL server.I have read the VDI specifications. I want to know whether I can use the VDI just to freeze and thaw without taking the snaphot and the backup. Or is there any other way to do the same ?
Thanks in Advance.
I have the fields in my table:
ID, Title, Description, Price, ImageData, Active
I only want it to be possible to have two records at any time with Active=Yes. Active is a Bit, Yes/No, field.
Any help is appreciated.
Chuck
hi,i have a union statement that works like a charm:SELECT
[Name],
[EventID] AS [ItemID],
[TourID],
[Date],
NULL AS [StartDate],
[Date] AS [SortDate],
[Type] FROM [Events]
WHERE
[TourID] IS NULL
AND
AccessLevel <= @AuthenticationLevel
UNION
SELECT
[Name],
[TourID] AS [ItemID],
[TourID],
NULL AS [Date],
[StartDate],
[StartDate] AS [SortDate],
'2' AS [Type]
FROM
[Tours]
WHERE
AccessLevel <= @AuthenticationLevel
ORDER BY
[SortDate] this statement selects all the records i want from both tables, and orders the entire new table by sort date.what i want to do is to add a limitation on the number of records. i have tried using both LIMIT and TOP commands, but i can't get it working. both of the below statements give me a syntax error saying "Incorrect syntax near the keyword 'ORDER'"first try:SELECT * FROM (<same_statement_as_above>) ORDER BY [SortDate] LIMIT 2second try:SELECT TOP 2 * FROM (<same_statement_as_above>) ORDER BY [SortDate]what am i doing wrong, and how do i achieve what i want?i am working with mssql server 2005 (express locally, and standard on my hosting service). thanks for your help!
Hi,
I have an application with a DAL that has an interface with SQL Server.
The application has 400 users that open the web forms.
My question is:
Is there a limit of the parallel connections that can be opened? Or the IIS is managing all the access to the DB? Should I worry about the performance Or it's normal behaviour for ASP.NET applications?
Thanks
hello everybody
I created user "MyUser " with rights
1. public for database "Orders"
2. select, update,delete, insert to table Orders.dbo.PersonalInfo
I have table Orders.dbo.PersonalInfoChangeLog
( it keeps information on any update on Orders.dbo.PersonalInfo including
HOST_NAME())
So I don't want anybody to see even structure of this table(Orders.dbo.PersonalInfo ).
if person loged as "Myuser" he can use
1. sp_help PersonalInfoChangeLog
2.Enterpise Manager to see properties of the table
How can limit rights to see structure without generating Application role ?
Thanks
I was wondering if anyone out there can help me as to how to limit the query results to show only the authors who have written more than one book?
SELECT a.au_lname, au_fname, COUNT(t.title_id)
FROM pubs..authors a
INNER JOIN pubs..titleauthor t ON (a.au_id=t.au_id)
GROUP BY a.au_lname, au_fname
Regards,
someone suggested I use:
select top 20 * from t
where pkc not in (select top 10 pkc from t order by pkc) order by pkc
to simulate the limit function in mysql.
i want to replace the 10 in the inner select with a variable. When I do this:
select top 20 * from t
where pkc not in (select top @counter pkc from t order by pkc) order by pkc
it gives me in an error in sproc. Pls help!!
There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?
hi everybody,
Beside top statement what is the best statement used to limit the display the data?
Thanks...
I have an application that uses a small SQL Server database.
What I need is to prevent the access of an amount of users to the database.
At the beginning I want to limit the access only for 2 users but I want to be able in the future to grant the access to more.