• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

High Memory utilisation and slow apps

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
gbarnes0990
Asked:
gbarnes0990
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
LowfatspreadCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
gbarnes0990Author Commented:
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
 
imran_fastCommented:
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
 
ptjcbCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now