[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

High Memory utilisation and slow apps

Posted on 2006-05-03
6
Medium Priority
?
718 Views
Last Modified: 2010-08-05
I am not sure if this is a server problem or a SQL problem but hopefully someone can help. We have a server with around 3 SQL databases running on it. The users of the applications that are accessing these databases are complaining that it is slow. I have checked the server and it seems that the memory utilisation was at 73% constant. A defrag and a reboot of the server was performed last night and I have checked the server this morning. Memory utilisation is now 60% constant but it seems to be rising by about 1% every 5 hours. I have spoken to the users this morning and they are complaining that the applications are still slow although not as bad. I have checked the event viewer and it has many of the following errors:-

ODBC error encountered, State = 23000, native error = 515, error message = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails..

The data portion of event 19000 from MSSQLSERVER is invalid.

Can anyone help?
0
Comment
Question by:gbarnes0990
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16594558
>I have checked the event viewer and it has many of the following errors:-
>ODBC error encountered, State = 23000, native error = 515, error message = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value >NULL into column '', table ''; column does not allow nulls. INSERT fails..
>The data portion of event 19000 from MSSQLSERVER is invalid.

that error is an application error, as the table does not permit to insert a NULL value into the table, but the application tries to do so.
solution:
either change the application
or change the table to accepts nulls

Note that you should not use the ODBC driver to connect to sql server, but rather the oledb provider as it is faster.

now, if the applications are generally slow, this is mostly due to:
1) missing indexes in the tables, resp queries that are poorly designed
2) bad application coding, like getting the entire table contents only to add a new or show a single record...


0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16594717
Rise in memory is not necessary an anomaly as SQL Server uses big memory foortprints for IO/RAM swapping

<<ODBC error encountered, State = 23000, native error = 515, error message = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails..>>  This is a design integrity issue not a server functionning issue...Hope this helps...
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 16594779
is the server dedicated to SQL Server or do you run other apps on it...

rather than a server defrag, do you run regular reorganisations on your actual databases...

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:gbarnes0990
ID: 16594850
I'm sorry my SQL knowledge only goes as far as backup and restore so I don't know about reorganisations. From what I can tell the server is only running SQL duriing the day and has 3 databases on it. The applications associated to the databases are run from other servers. The only other apps that appear to be on the server are HP agents and Backup Exec which runs at 2am in the morning. One of the users has now arranged for a developer of one of the systems to come in tomorrow. I have forwarded on the error messages and everyones comments above, hopefully they will find a solution.
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 750 total points
ID: 16595686
try using Sql Profiler and grap the quries that are taking time you can start the profiler from
start menu ==> programs==> microsoft sql server ==> profiler

enter the sa password or login with NT user having Sysadmin prive. on sql server and run the profiler.

when you get the result try running INdex Tuning Wizard and see which u can find in sql profiler tools index tuning wizard use this wizard to index the tables in your database.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16595764
You are doing the right things - checking on the system and creating a baseline (you have focused on the memory utilization - but you also should check networks, connections, page swapping, and locks. This link has many articles that will help you: http://www.sql-server-performance.com/

Users complaining that the system is slow is vague and does not give you a clear way to respond. What are the users doing? Are they all trying to do the same thing? Are they accessing the same tables? Are they running long queries or transactions?

Does your app use inline sql or stored procedures? Do you use explicit or implicit transactions? Are your indexes still good? How often do you reindex the databases involved?

Just some thoughts....



0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question